using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Reflection;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.Windows.Forms;
using System.Workflow.Activities;
using System.Xml;
using System.Xml.Schema;
using System.Xml.Serialization;
using OpenComposite.Base;
using OpenComposite.Base.Collections;
using OpenComposite.HumanActivity.Contracts;
using OpenComposite.Mapper;
using OpenComposite.UserAccess;
using OpenComposite.Repository.Properties;
using OpenComposite.EII.Repository;
using OpenComposite.EII.Services;

namespace OpenComposite.EII
{
	public class DataAccess : DataAccessBase
	{
		#region Initialization

		public DataAccess(IServiceProvider serviceProvider, UserAccessController useraccess)
			: base(useraccess.ConnectString)
		{
			_useraccess = useraccess;
			_serviceProvider = serviceProvider;
		}

		#endregion

		#region Public Properties

		/// <summary>
		/// Gets the user access.
		/// </summary>
		/// <value>The user access.</value>
		public UserAccessController UserAccess
		{
			get { return _useraccess; }
		}

		/// <summary>
		/// Gets the repository service.
		/// </summary>
		/// <value>The repository service.</value>
		public RepositoryService RepositoryService
		{
			get { return (RepositoryService)_serviceProvider.GetService(typeof(RepositoryService)); }
		}

		/// <summary>
		/// Gets the DB connection.
		/// </summary>
		/// <value>The DB connection.</value>
		public SqlConnection DBConnection
		{
			get { return _conn; }
		}

		#endregion

		#region Methods

		#region Generic Methods

		/// <summary>
		/// Converts the type of the XSD to DOTNET data.
		/// </summary>
		/// <param name="xsddatatype">The xsddatatype.</param>
		/// <returns></returns>
		public string ConvertXSDToDOTNETDataType(string xsddatatype)
		{
			string dotnetdatatype = "System.String";
			object obj = execScalar("SELECT datatype_dotnet FROM datatypes WHERE datatype = @datatype",
									new SqlParameter("@datatype", xsddatatype));
			if ( obj != null && obj is string )
				dotnetdatatype = (string)obj;

			return dotnetdatatype;
		}
		/// <summary>
		/// Gets the type of the DOTNET typeof XSD data.
		/// </summary>
		/// <param name="xsddatatype">The xsddatatype.</param>
		/// <returns></returns>
		public Type GetDOTNETTypeofXSDDataType(string xsddatatype)
		{
			string dotnetDT = ConvertXSDToDOTNETDataType(xsddatatype);
			Type t = Type.GetType(dotnetDT, false);
			if ( t == null )
				t = typeof(string);
			return t;
		}
		/// <summary>
		/// Converts the string.
		/// </summary>
		/// <param name="valueString">The value string.</param>
		/// <param name="xsdDataType">Type of the XSD data.</param>
		/// <returns></returns>
		public object ConvertString(string valueString, string xsdDataType)
		{
			Type t = GetDOTNETTypeofXSDDataType(xsdDataType);
			object obj = null;
			try {
				obj = Convert.ChangeType(valueString, t);
			} catch {
				obj = null;
			}
			return obj;
		}
		/// <summary>
		/// Checks the business rule root categories.
		/// </summary>
		/// <returns></returns>
		[Obsolete]
		public bool CheckBRuleRootCategories()
		{
			bool bSuccess = true;
			//try {
			//    DataTable dt = this.BusinessRuleData.GetRulesTopCategories(this.ProcessData.UserData.OrganizationID);
			//    if ( dt != null && dt.Rows.Count < 3 ) {
			//        string[] cat = { BusinessRuleMasterName.BusinessRule, 
			//                     BusinessRuleMasterName.ParticipantRule,
			//                     BusinessRuleMasterName.BusinessPartnerRule};

			//        for ( int i = 0; i < 3; i++ ) {
			//            if ( dt.Select("name = '" + cat[i] + "'").Length == 0 ) {
			//                BusinessRule rule = new BusinessRule(cat[i]);
			//                rule.RuleType = i.ToString();
			//                rule.Type = BusinessRuleType.Category;
			//                rule.ParentRule = String.Empty;
			//                this.BusinessRuleData.InsertRuleOrCategory(rule, this.ProcessData.UserData.OrganizationID);
			//            }
			//        }
			//    }
			//    bSuccess = true;
			//} catch (Exception ex) {
			//    Utils.ShowException("Could not Insert Default Top-Categories into Database", ex);
			//    bSuccess = false;
			//}
			return bSuccess;
		}

		#endregion

		#region Repository

		/// <summary>
		/// Gets the repository types.
		/// </summary>
		/// <returns></returns>
		public DataTable GetRepositoryTypes()
		{
			return fillDataTable("SELECT * FROM repository_types");
		}

		/// <summary>
		/// Selects the repository table.
		/// </summary>
		/// <param name="itemtype">The itemtype.</param>
		/// <returns></returns>
		public DataTable SelectRepositoryTable(ItemType itemtype)
		{
			string query;

			if ( _dicSelectAllQueries.ContainsKey(itemtype) ) {
				query = _dicSelectAllQueries[itemtype];
			} else {
				// generate select query
				query = string.Format("SELECT * FROM {0} WHERE org_id = @org_id", getTableName(itemtype));
				if ( hasDeletedColumn(itemtype) )
					query += " AND [is_deleted] = 0";
				_dicSelectAllQueries.Add(itemtype, query);
			}
			return fillDataTable(query,
				new SqlParameter("@org_id", Global.Data.UserAccess.UserData.OrganizationID));
		}
		/// <summary>
		/// Selects the repository table by domain.
		/// </summary>
		/// <param name="itemtype">The itemtype.</param>
		/// <param name="domainId">The domain ID.</param>
		/// <returns></returns>
		public DataTable SelectRepositoryTableByDomain(ItemType itemtype, int domainId)
		{
			string query;

			if ( _dicSelectAllDomainQueries.ContainsKey(itemtype) ) {
				query = _dicSelectAllDomainQueries[itemtype];
			} else {
				// generate select query
				query = string.Format(
					"SELECT * FROM {0} WHERE org_id = @org_id and domain_id = @domain_id",
					getTableName(itemtype));
				if ( hasDeletedColumn(itemtype) )
					query += " AND [is_deleted] = 0";
				_dicSelectAllDomainQueries.Add(itemtype, query);
			}
			return fillDataTable(query,
				new SqlParameter("@org_id", Global.CurrentUser.OrganizationID),
				new SqlParameter("@domain_id", (object)domainId));
		}
		/// <summary>
		/// Selects the repository table complete.
		/// </summary>
		/// <param name="itemtype">The itemtype.</param>
		/// <returns></returns>
		public DataTable SelectRepositoryTableComplete(ItemType itemtype)
		{
			string query;

			if ( _dicSelectAllCompleteQueries.ContainsKey(itemtype) ) {
				query = _dicSelectAllCompleteQueries[itemtype];
			} else {
				// generate select query
				query = string.Format("SELECT * FROM {0}", getTableName(itemtype));
				if ( hasDeletedColumn(itemtype) )
					query += " AND [is_deleted] = 0";
				_dicSelectAllCompleteQueries.Add(itemtype, query);
			}
			return fillDataTable(query);
		}
		/// <summary>
		/// Selects the repository item.
		/// </summary>
		/// <param name="itemtype">The itemtype.</param>
		/// <param name="id">The ID.</param>
		/// <param name="isDeleted">if set to <c>true</c> [is deleted].</param>
		/// <returns></returns>
		internal DataRow SelectRepositoryItem(ItemType itemtype, int id, out bool isDeleted)
		{
			isDeleted = false;
			string idcolnm = getIDColumnName(itemtype);
			string query;

			if ( _dicSelectItemQueries.ContainsKey(itemtype) ) {
				query = _dicSelectItemQueries[itemtype];
			} else {
				// generate select query
				query = string.Format(
					"SELECT * FROM {0} WHERE [{1}] = @{1}",
					getTableName(itemtype), idcolnm);
				//if ( hasDeletedColumn(itemtype) )
				//    query += " AND [is_deleted] = 0";
				_dicSelectItemQueries.Add(itemtype, query);
			}
			DataTable dt = fillDataTable(query, new SqlParameter("@" + idcolnm, id));
			if ( dt != null && dt.Rows.Count > 0 && dt.Columns.Count > 1 ) {
				if ( hasDeletedColumn(itemtype) && (bool)dt.Rows[0]["is_deleted"] ) {
					isDeleted = true;
					return dt.Rows[0];
				}
				return dt.Rows[0];
			} else
				return null;
		}
		/// <summary>
		/// Deletes the repository item.
		/// </summary>
		/// <param name="itemtype">The itemtype.</param>
		/// <param name="id">The ID.</param>
		/// <returns></returns>
		internal bool DeleteRepositoryItem(ItemType itemtype, int id)
		{
			string idcolnm = getIDColumnName(itemtype);
			string query;

			if ( _dicDeleteQueries.ContainsKey(itemtype) ) {
				query = _dicDeleteQueries[itemtype];
			} else {
				// generate delete query
				query = string.Format(
					"DELETE FROM {0} WHERE [{1}] = @{1}",
					getTableName(itemtype), idcolnm);
				_dicDeleteQueries.Add(itemtype, query);
			}
			return ( execNonQuery(query, new SqlParameter("@" + idcolnm, id)) > 0 );
		}
		/// <summary>
		/// Updates the repository item.
		/// </summary>
		/// <param name="itemtype">The itemtype.</param>
		/// <param name="row">The row.</param>
		/// <returns></returns>
		internal DataRow UpdateRepositoryItem(ItemType itemtype, DataRow row)
		{
			string idcolnm = getIDColumnName(itemtype);
			string queryUpdate, updateSet = "", insertCol = "", insertVal = "";
			List<SqlParameter> sqlParams = new List<SqlParameter>(row.ItemArray.Length);
			//if ( row.IsNull(idcolnm) )
			//    return null;

			if ( _dicUpdateQueries.ContainsKey(itemtype) ) {
				queryUpdate = _dicUpdateQueries[itemtype];
				foreach ( DataColumn col in row.Table.Columns ) {
					// add sql parameter
					SqlParameter param = new SqlParameter(string.Format("@{0}", col.ColumnName), row[col]);
					if ( col.DataType == typeof(byte[]) )
						param.SqlDbType = SqlDbType.Image;
					sqlParams.Add(param);
				}
			} else {
				foreach ( DataColumn col in row.Table.Columns ) {
					// generate update set
					if ( col.ColumnName != idcolnm ) {
						if ( updateSet != "" ) updateSet += ",";
						updateSet += string.Format("[{0}] = @{0}", col.ColumnName);

						// generate insert columns
						if ( insertCol != "" ) insertCol += ",";
						insertCol += string.Format("[{0}]", col.ColumnName);

						// generate insert values
						if ( insertVal != "" ) insertVal += ",";
						insertVal += string.Format("@{0}", col.ColumnName);

					}
					// add sql parameter
					SqlParameter param = new SqlParameter(string.Format("@{0}", col.ColumnName), (object)row[col]);
					if ( col.DataType == typeof(byte[]) )
						param.SqlDbType = SqlDbType.Image;
					sqlParams.Add(param);
				}
				// generate update query
				queryUpdate = string.Format(
					"IF EXISTS ( SELECT {1} FROM {0} WHERE [{1}] = @{1} ) " +
					"BEGIN " +
						"UPDATE {0} SET {2} WHERE [{1}] = @{1}; " +
						"SELECT @{1} " +
					"END " +
					"ELSE " +
					"BEGIN " +
						"INSERT INTO {0} ({3}) VALUES ({4}); " +
						"SELECT {1} FROM {0} WHERE {1} = SCOPE_IDENTITY() " +
					"END",
					getTableName(itemtype), idcolnm, updateSet, insertCol, insertVal);
				_dicUpdateQueries.Add(itemtype, queryUpdate);
			}
			int id = (int)execScalar(queryUpdate, sqlParams.ToArray());
			bool isDeleted;
			return SelectRepositoryItem(itemtype, id, out isDeleted);
		}
		/// <summary>
		/// Gets the new repository item data row.
		/// </summary>
		/// <param name="itemtype">The itemtype.</param>
		/// <returns></returns>
		internal DataRow GetNewRepositoryItemDataRow(ItemType itemtype)
		{
			string query;

			if ( _dicSelectForNewQueries.ContainsKey(itemtype) ) {
				query = _dicSelectForNewQueries[itemtype];
			} else {
				// generate select query
				query = string.Format("SELECT * FROM {0} WHERE {1} IS NULL",
					getTableName(itemtype), getIDColumnName(itemtype));
				_dicSelectForNewQueries.Add(itemtype, query);
			}
			DataTable dt = fillDataTable(query);
			DataRow row = null;
			if ( dt != null && dt.Columns.Count > 1 ) {
				row = dt.NewRow();
				if ( dt.Columns.Contains("org_id") && itemtype != ItemType.Organization ) {
					row["org_id"] = Global.Data.UserAccess.UserData.OrganizationID;
				}
			}
			return row;
		}

		#region Queries: AddRepositoryHierarchy
		private const string SQL_AddRepositoryHierarchy_Items =
			"SET ANSI_NULLS OFF; " +
			"IF NOT EXISTS (SELECT * FROM repository_hierarchy " +
				"WHERE parent_reptype_id = @parent_reptype_id AND parent_id = @parent_id AND " +
					"child_reptype_id = @child_reptype_id AND child_id = @child_id AND " +
					"repository_id = @repository_id) " +
			"BEGIN " +
				"INSERT INTO repository_hierarchy " +
				" (parent_reptype_id, parent_id, parent_display, parent_image_key, parent_selected_image_key, " +
				"  child_reptype_id, child_id, child_display, child_image_key, child_selected_image_key, " +
				"  repository_id, position) " +
				"VALUES " +
				" (@parent_reptype_id, @parent_id, @parent_display, @parent_image_key, @parent_selected_image_key, " +
				"  @child_reptype_id, @child_id, @child_display, @child_image_key, @child_selected_image_key, " +
				"  @repository_id, @position) " +
			"END ELSE BEGIN " +
				"UPDATE repository_hierarchy SET " +
					"parent_display = @parent_display, " +
					"parent_image_key = @parent_image_key, " +
					"parent_selected_image_key = @parent_selected_image_key, " +
					"child_display = @child_display, " +
					"child_image_key = @child_image_key, " +
					"child_selected_image_key = @child_selected_image_key, " +
					"position = @position " +
				"WHERE parent_reptype_id = @parent_reptype_id AND parent_id = @parent_id AND " +
					"child_reptype_id = @child_reptype_id AND child_id = @child_id AND " +
					"repository_id = @repository_id " +
			"END; " +
			"SET ANSI_NULLS ON";
		private const string SQL_AddRepositoryHierarchy_Values =
			"SET ANSI_NULLS OFF; " +
			"IF NOT EXISTS (SELECT * FROM repository_hierarchy " +
			" WHERE parent_reptype_id = @parent_reptype_id AND parent_id = @parent_id AND " +
			" child_reptype_id = @child_reptype_id AND child_id = @child_id AND " +
			" repository_id = @repository_id) " +
			"BEGIN " +
				"INSERT INTO repository_hierarchy " +
				" (parent_reptype_id, parent_id, child_reptype_id, child_id, child_display, repository_id) " +
				"VALUES " +
				" (@parent_reptype_id, @parent_id, @child_reptype_id, @child_id, @child_display, " +
					"@repository_id) " +
			"END; " +
			"SET ANSI_NULLS ON";
		#endregion
		/// <summary>
		/// Adds the repository hierarchy.
		/// </summary>
		/// <param name="parentItem">The parent item.</param>
		/// <param name="childItem">The child item.</param>
		/// <param name="repositoryId">The repository ID.</param>
		/// <returns></returns>
		public bool AddRepositoryHierarchy(IRepositoryItem parentItem,
			IRepositoryItem childItem, int repositoryId)
		{
			return AddRepositoryHierarchy(parentItem, childItem, repositoryId, 0);
		}
		/// <summary>
		/// Adds the repository hierarchy.
		/// </summary>
		/// <param name="parentItem">The parent item.</param>
		/// <param name="childItem">The child item.</param>
		/// <param name="repositoryId">The repository ID.</param>
		/// <param name="position">The position.</param>
		/// <returns></returns>
		public bool AddRepositoryHierarchy(IRepositoryItem parentItem,
			IRepositoryItem childItem, int repositoryId, int position)
		{
			if ( childItem == null ) {
				System.Diagnostics.Debug.Assert(childItem != null, "AddRepositoryHierarchy: childItem == null", "");
				return false;
				//throw new ArgumentNullException("childItem");
			}
			int result = execNonQuery(
				SQL_AddRepositoryHierarchy_Items,
				new SqlParameter("@parent_reptype_id", ( parentItem == null ? (object)DBNull.Value : (object)(int)parentItem.RepositoryItemType )),
				new SqlParameter("@parent_id", ( parentItem == null ? (object)DBNull.Value : (object)parentItem.ID )),
				new SqlParameter("@parent_display", ( parentItem == null ? (object)DBNull.Value : (object)parentItem.Display )),
				new SqlParameter("@parent_image_key", ( parentItem == null ? (object)DBNull.Value : (object)parentItem.ImageKey )),
				new SqlParameter("@parent_selected_image_key", ( parentItem == null ? (object)DBNull.Value : (object)parentItem.SelectedImageKey )),
				new SqlParameter("@child_reptype_id", (int)childItem.RepositoryItemType),
				new SqlParameter("@child_id", childItem.ID),
				new SqlParameter("@child_display", childItem.Display),
				new SqlParameter("@child_image_key", childItem.ImageKey),
				new SqlParameter("@child_selected_image_key", childItem.SelectedImageKey),
				new SqlParameter("@repository_id", repositoryId),
				new SqlParameter("@position", position));
			bool ok = ( result > 0 );
			if ( ok ) {
				IRepositoryChangeService changeSvc = null;
				if ( parentItem != null )
					changeSvc = parentItem.GetService(typeof(IRepositoryChangeService)) as IRepositoryChangeService;
				else if ( childItem != null )
					changeSvc = childItem.GetService(typeof(IRepositoryChangeService)) as IRepositoryChangeService;
				if ( changeSvc != null ) {
					if ( parentItem != null )
						changeSvc.OnRepositoryChanged(parentItem, Global.Data.GetType().GetMethod("AddRepositoryHierarchy", new Type[] { typeof(RepositoryItemBase), typeof(RepositoryItemBase), typeof(int) }), null, "Parent");
					if ( childItem != null )
						changeSvc.OnRepositoryChanged(childItem, Global.Data.GetType().GetMethod("AddRepositoryHierarchy", new Type[] { typeof(RepositoryItemBase), typeof(RepositoryItemBase), typeof(int) }), null, "Child");
				}
			}
			return ok;
		}
		/// <summary>
		/// Adds the repository hierarchy.
		/// </summary>
		/// <param name="parentType">Type of the parent.</param>
		/// <param name="parentId">The parent ID.</param>
		/// <param name="childType">Type of the child.</param>
		/// <param name="childId">The child ID.</param>
		/// <param name="childDisplay">The child display.</param>
		/// <param name="repositoryId">The repository ID.</param>
		/// <returns></returns>
		public bool AddRepositoryHierarchy(ItemType parentType, int parentId,
			ItemType childType, int childId, string childDisplay, int repositoryId)
		{
			int result = execNonQuery(
				SQL_AddRepositoryHierarchy_Values,
				new SqlParameter("@parent_reptype_id", ( parentType == ItemType.Undefined ? (object)DBNull.Value : (object)parentType )),
				new SqlParameter("@parent_id", ( parentId <= 0 ? (object)DBNull.Value : (object)parentId )),
				new SqlParameter("@child_reptype_id", childType),
				new SqlParameter("@child_id", childId),
				new SqlParameter("@child_display", childDisplay),
				new SqlParameter("@repository_id", repositoryId));
			return ( result > 0 );
		}

		/// <summary>
		/// Gets the repository hierarchy.
		/// </summary>
		/// <param name="repositoryId">The repository ID.</param>
		/// <returns></returns>
		public DataTable GetRepositoryHierarchy(int repositoryId)
		{
			return fillDataTable(
				"SELECT * FROM repository_hierarchy WHERE repository_id = @repository_id",
				new SqlParameter("@repository_id", repositoryId));
		}
		/// <summary>
		/// Gets the repository hierarchy children.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <returns></returns>
		public DataTable GetRepositoryHierarchyChildren(IRepositoryItem item)
		{
			if ( item == null )
				return GetRepositoryHierarchyChildren(ItemType.Undefined, 0);
			else
				return GetRepositoryHierarchyChildren(item.RepositoryItemType, item.ID);
		}
		/// <summary>
		/// Gets the repository hierarchy children.
		/// </summary>
		/// <param name="type">The type.</param>
		/// <param name="id">The ID.</param>
		/// <returns></returns>
		public DataTable GetRepositoryHierarchyChildren(ItemType type, int id)
		{
			if ( type == ItemType.Undefined )
				return fillDataTable(
					"SELECT rh.*, " +
						"(SELECT Count(*) " +
						" FROM repository_hierarchy rh1 " +
						" WHERE rh1.parent_reptype_id = rh.child_reptype_id AND rh1.parent_id = rh.child_id) AS child_count " +
					"FROM repository_hierarchy rh " +
					"WHERE rh.parent_reptype_id IS NULL AND rh.parent_id IS NULL ");
			else
				return fillDataTable(
					"SELECT rh.*, " +
						"(SELECT Count(*) " +
						" FROM repository_hierarchy rh1 " +
						" WHERE rh1.parent_reptype_id = rh.child_reptype_id AND rh1.parent_id = rh.child_id) AS child_count " +
					"FROM repository_hierarchy rh " +
					"WHERE rh.parent_reptype_id = @parent_reptype_id AND rh.parent_id = @parent_id",
					new SqlParameter("@parent_reptype_id", (int)type),
					new SqlParameter("@parent_id", id));
		}
		/// <summary>
		/// Gets the repository hierarchy parents list.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <returns></returns>
		public BindingListEx<IRepositoryItem> GetRepositoryHierarchyParentsList(IRepositoryItem item)
		{
			BindingListEx<IRepositoryItem> parents = new BindingListEx<IRepositoryItem>();
			DataTable dt = GetRepositoryHierarchyParents(item);

			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					IRepositoryItem ri = this.RepositoryService.GetItem(
						(ItemType)row["parent_reptype_id"], (int)row["parent_id"], false);
					if ( ri != null && !parents.Contains(ri) ) parents.Add(ri);
				}
			}

			return parents;
		}
		/// <summary>
		/// Gets the repository hierarchy parents.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <returns></returns>
		public DataTable GetRepositoryHierarchyParents(IRepositoryItem item)
		{
			if ( item != null )
				return fillDataTable(
					"SELECT * FROM repository_hierarchy " +
					"WHERE child_reptype_id = @child_reptype_id AND child_id = @child_id",
					new SqlParameter("@child_reptype_id", (int)item.RepositoryItemType),
					new SqlParameter("@child_id", item.ID));
			else
				return new DataTable();
		}

		/// <summary>
		/// Gets the repository hierarchy child repository list.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <returns></returns>
		public DataTable GetRepositoryHierarchyChildRepositoryList(IRepositoryItem item)
		{
			return GetRepositoryHierarchyChildRepositoryList(item.RepositoryItemType, item.ID);
		}
		/// <summary>
		/// Gets the repository hierarchy child repository list.
		/// </summary>
		/// <param name="type">The type.</param>
		/// <param name="id">The ID.</param>
		/// <returns></returns>
		public DataTable GetRepositoryHierarchyChildRepositoryList(ItemType type, int id)
		{
			DataSet ds = null;
			// TODO: create own table & correct the search procedure

			if ( type == ItemType.Undefined )
				ds = execProcedure(
					"get_repository_child_items",
					new SqlParameter("@type_id", DBNull.Value),
					new SqlParameter("@item_id", DBNull.Value));
			else if ( id <= 0 )
				ds = execProcedure(
					"get_repository_child_items",
					new SqlParameter("@type_id", (int)type),
					new SqlParameter("@item_id", DBNull.Value));
			else
				ds = execProcedure(
					"get_repository_child_items",
					new SqlParameter("@type_id", (int)type),
					new SqlParameter("@item_id", id));

			if ( ds != null && ds.Tables.Count > 0 )
				return ds.Tables[0];
			else
				return null;
		}
		/// <summary>
		/// Get a DataTable with repository items with the spezified properties.
		/// </summary>
		/// <param name="type">The ItemType of the parent item.</param>
		/// <param name="id">The id of the parent item.</param>
		/// <param name="org_id">The organization of the requested items. Use &lt;= 0 for all organizations.</param>
		/// <param name="childtype">The ItemType of the requested items. Use ItemType.Undefined for all child types.</param>
		/// <returns>Returns a DataTable.</returns>
		public DataTable GetRepositoryHierarchyChildRepositoryList(ItemType type, int id,
			int org_id, ItemType childtype)
		{
			DataSet ds = null;
			if ( type == ItemType.Undefined )
				ds = execProcedure(
					"get_repository_child_items",
					new SqlParameter("@type_id", (object)0),
					new SqlParameter("@item_id", (object)0),
					new SqlParameter("@org_id", (object)org_id),
					new SqlParameter("@child_type_id", (object)childtype));
			else if ( id <= 0 )
				ds = execProcedure(
					"get_repository_child_items",
					new SqlParameter("@type_id", (int)type),
					new SqlParameter("@item_id", (object)0),
					new SqlParameter("@org_id", org_id),
					new SqlParameter("@child_type_id", (object)childtype));
			else
				ds = execProcedure(
					"get_repository_child_items",
					new SqlParameter("@type_id", (int)type),
					new SqlParameter("@item_id", id),
					new SqlParameter("@org_id", org_id),
					new SqlParameter("@child_type_id", (object)childtype));

			if ( ds != null && ds.Tables.Count > 0 )
				return ds.Tables[0];
			else
				return null;
		}

		#region Query GetRepositoryHierarchyChildRepositoryList
		private const string _SQL_SELECTCHILDREPLISTBYTYPE =
			"SELECT * FROM {0} " +
			"WHERE {1} in ( " +
				"SELECT child_id FROM repository_hierarchy " +
				"WHERE child_reptype_id = @childtype AND " +
					"parent_reptype_id = @parenttype AND parent_id = @parentid " +
			")";
		#endregion
		/// <summary>
		/// Gets the repository hierarchy child repository list.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <param name="childtype">The childtype.</param>
		/// <returns></returns>
		public DataTable GetRepositoryHierarchyChildRepositoryList(IRepositoryItem item, ItemType childtype)
		{
			if ( item == null )
				return null;
			else
				return GetRepositoryHierarchyChildRepositoryList(item.RepositoryItemType, item.ID, childtype);
		}
		/// <summary>
		/// Gets the repository hierarchy child repository list.
		/// </summary>
		/// <param name="type">The type.</param>
		/// <param name="id">The ID.</param>
		/// <param name="childtype">The childtype.</param>
		/// <returns></returns>
		public DataTable GetRepositoryHierarchyChildRepositoryList(ItemType type, int id, ItemType childtype)
		{
			string tblnm = getTableName(childtype);
			string idcolnm = getIDColumnName(childtype);

			return fillDataTable(
				string.Format(_SQL_SELECTCHILDREPLISTBYTYPE, tblnm, idcolnm),
				new SqlParameter("@childtype", (int)childtype),
				new SqlParameter("@parenttype", (int)type),
				new SqlParameter("@parentid", id));
		}
		/// <summary>
		/// Gets the repository hierarchy child repository list.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="item">The item.</param>
		/// <returns></returns>
		public BindingListEx<T> GetRepositoryHierarchyChildRepositoryList<T>(IRepositoryItem item) where T : class, IRepositoryItem
		{
			ItemType childtype = getItemTypeFromIRepositoryItemClass<T>();
			return GetRepositoryHierarchyChildRepositoryList<T>(item.RepositoryItemType, item.ID, childtype);
		}
		/// <summary>
		/// Gets the repository hierarchy child repository list.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="type">The type.</param>
		/// <param name="id">The ID.</param>
		/// <param name="childtype">The childtype.</param>
		/// <returns></returns>
		public BindingListEx<T> GetRepositoryHierarchyChildRepositoryList<T>(ItemType type, int id, ItemType childtype)
		{
			BindingListEx<T> list = new BindingListEx<T>();
			DataTable dt = GetRepositoryHierarchyChildRepositoryList(type, id, childtype);

			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					IRepositoryItem baseitem = RepositoryService.GetItem(childtype, row);
					if ( baseitem != null && baseitem.ID > 0 && baseitem is T ) {
						T typeditem = (T)Convert.ChangeType(baseitem, typeof(T));
						if ( typeditem != null ) {
							list.Add(typeditem);
						}
					}
				}
			}

			return list;
		}

		/// <summary>
		/// Get a DataTable with a list of all items of a specific ItemType.
		/// </summary>
		/// <param name="idRepository">A repository id.</param>
		/// <param name="type">An ItemType.</param>
		/// <returns>A DataTable with the columns: reptype_id, id and display.</returns>
		public DataTable GetRepositoryItemsOfItemType(int idRepository, ItemType type)
		{
			return fillDataTable(
				"SELECT child_reptype_id AS [reptype_id], child_id AS [id], child_display AS [display] " +
				"FROM repository_hierarchy " +
				"WHERE repository_id = @repid AND child_reptype_id = @type",
				new SqlParameter("@repid", idRepository),
				new SqlParameter("@type", (int)type));
		}

		/// <summary>
		/// Gets the repository items.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <returns></returns>
		public BindingListEx<T> GetRepositoryItems<T>() where T : class, IRepositoryItem
		{
			ItemType type = getItemTypeFromIRepositoryItemClass<T>();
			DataTable dt = SelectRepositoryTable(type);
			BindingListEx<T> items = new BindingListEx<T>();
			if ( dt == null || dt.Rows.Count == 0 ) return items;

			foreach ( DataRow row in dt.Rows ) {
				T item = this.RepositoryService.GetItem(type, row) as T;
				if ( item != null && !string.IsNullOrEmpty(item.Name) ) items.Add(item);
			}

			return items;
		}
		/// <summary>
		/// Gets the repository items of repository.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="repositoryId">The repository ID.</param>
		/// <returns></returns>
		public BindingListEx<T> GetRepositoryItemsOfRepository<T>(int repositoryId) where T : class, IRepositoryItem
		{
			ItemType type = getItemTypeFromIRepositoryItemClass<T>();
			DataTable dt = GetRepositoryItemsOfItemType(repositoryId, type);
			BindingListEx<T> items = new BindingListEx<T>();
			if ( dt == null || dt.Rows.Count == 0 ) return items;

			foreach ( DataRow row in dt.Rows ) {
				if ( row["id"] is int ) {
					T item = this.RepositoryService.GetItem<T>((int)row["id"]);
					if ( item != null ) items.Add(item);
				}
			}

			return items;
		}
		public BindingListEx<T> GetRepositoryItemsOfDomain<T>(int domainId) where T : class, IRepositoryItem
		{
			ItemType type = getItemTypeFromIRepositoryItemClass<T>();
			DataTable dt = SelectRepositoryTableByDomain(type, domainId);
			BindingListEx<T> items = new BindingListEx<T>();
			if ( dt == null || dt.Rows.Count == 0 ) return items;

			foreach ( DataRow row in dt.Rows ) {
				T item = this.RepositoryService.GetItem(type, row) as T;
				if ( item != null ) items.Add(item);
			}

			return items;
		}
		/// <summary>
		/// Gets the repository parents of a special type.
		/// </summary>
		/// <param name="type">The type.</param>
		/// <param name="id">The ID.</param>
		/// <param name="typeToSearchFor">The type to search for.</param>
		/// <returns></returns>
		public DataTable GetRepositoryParentsOfType(ItemType type, int id, ItemType typeToSearchFor)
		{
			DataSet ds = execProcedure(
				"get_repository_parents_of_type",
				new SqlParameter("@reptype_id", (int)type),
				new SqlParameter("@item_id", id),
				new SqlParameter("@parent_reptype_id", (int)typeToSearchFor));

			if ( ds != null && ds.Tables.Count > 0 )
				return ds.Tables[0];
			else
				return null;
		}

		/// <summary>
		/// Adds the repository item to domain folder.
		/// </summary>
		/// <param name="domainId">The domain ID.</param>
		/// <param name="item">The item.</param>
		/// <returns></returns>
		public bool AddRepositoryItemToDomainFolder(int domainId, IRepositoryItem item)
		{
			Hashtable thRow = executeRow(
				"SELECT rh.child_id, rh.repository_id " +
				"FROM repository_hierarchy rh " +
				"INNER JOIN generic_folder gf " +
					"ON rh.child_reptype_id = @typeFolder AND gf.folder_id = rh.child_id " +
				"WHERE rh.parent_reptype_id = @typeDomain AND rh.parent_id = @domainId " +
					"AND (gf.folder_types = @folder_types OR " +
					string.Format("PATINDEX('{0};%', gf.folder_types) > 0 )"/* OR " +
								  "PATINDEX('%;{0};%', gf.folder_types) > 0 OR " +
								  "PATINDEX('%;{0}', gf.folder_types) > 0 )"*/
																			  , ( (int)item.RepositoryItemType ).ToString()),
				new SqlParameter("@typeFolder", (int)ItemType.GenericFolder),
				new SqlParameter("@typeDomain", (int)ItemType.Domain),
				new SqlParameter("@domainId", domainId),
				new SqlParameter("@folder_types", ( (int)item.RepositoryItemType ).ToString()));

			if ( thRow == null || thRow.Count == 0 )
				return false;

			if ( ExistsRepositoryHierarchy(ItemType.GenericFolder, (int)thRow["child_id"],
										  item.RepositoryItemType, item.ID,
										  (int)thRow["repository_id"]) ) {
				return true;
			} else {
				IRepositoryItem itemFolder = RepositoryService.GetItem(ItemType.GenericFolder, (int)thRow["child_id"], false);
				return AddRepositoryHierarchy(itemFolder, item, (int)thRow["repository_id"]);
			}

		}
		/// <summary>
		/// Adds the repository item to domain folder.
		/// </summary>
		/// <param name="domainId">The domain ID.</param>
		/// <param name="item">The item.</param>
		/// <param name="typeFolder">The type folder.</param>
		/// <returns></returns>
		public bool AddRepositoryItemToDomainFolder(int domainId, IRepositoryItem item, ItemType typeFolder)
		{
			Hashtable thRow = executeRow(
				"SELECT rh.child_id, rh.repository_id " +
				"FROM repository_hierarchy rh " +
				"INNER JOIN generic_folder gf " +
					"ON rh.child_reptype_id = @typeFolder AND gf.folder_id = rh.child_id " +
				"WHERE rh.parent_reptype_id = @typeDomain AND rh.parent_id = @domainId " +
					"AND (gf.folder_types = @folder_types OR " +
					string.Format("PATINDEX('{0};%', gf.folder_types) > 0 )"/* OR " +
								  "PATINDEX('%;{0};%', gf.folder_types) > 0 OR " +
								  "PATINDEX('%;{0}', gf.folder_types) > 0 )"*/
																			  , (int)typeFolder),
				new SqlParameter("@typeFolder", (int)ItemType.GenericFolder),
				new SqlParameter("@typeDomain", (int)ItemType.Domain),
				new SqlParameter("@domainId", domainId),
				new SqlParameter("@folder_types", ( (int)typeFolder ).ToString()));

			if ( thRow == null || thRow.Count == 0 )
				return false;

			if ( ExistsRepositoryHierarchy(ItemType.GenericFolder, (int)thRow["child_id"],
										  item.RepositoryItemType, item.ID,
										  (int)thRow["repository_id"]) ) {
				return true;
			} else {
				IRepositoryItem itemFolder = RepositoryService.GetItem(ItemType.GenericFolder, (int)thRow["child_id"], false);
				return AddRepositoryHierarchy(itemFolder, item, (int)thRow["repository_id"]);
			}

		}
		/// <summary>
		/// Gets the repository items of domain folder type.
		/// </summary>
		/// <param name="domainId">The domain ID.</param>
		/// <param name="type">The type.</param>
		/// <returns></returns>
		public DataTable GetRepositoryItemsOfDomainFolderType(int domainId, ItemType type)
		{
			Hashtable thRow = executeRow(
				"SELECT rh.child_id, rh.repository_id " +
				"FROM repository_hierarchy rh " +
				"INNER JOIN generic_folder gf " +
					"ON rh.child_reptype_id = @typeFolder AND gf.folder_id = rh.child_id " +
				"WHERE rh.parent_reptype_id = @typeDomain AND rh.parent_id = @domainId " +
					"AND (gf.folder_types = @folder_types OR " +
					string.Format("PATINDEX('{0};%', gf.folder_types) > 0 OR " +
								  "PATINDEX('%;{0};%', gf.folder_types) > 0 OR " +
								  "PATINDEX('%;{0}', gf.folder_types) > 0 )", ( (int)type ).ToString()),
				new SqlParameter("@typeFolder", (int)ItemType.GenericFolder),
				new SqlParameter("@typeDomain", (int)ItemType.Domain),
				new SqlParameter("@domainId", domainId),
				new SqlParameter("@folder_types", ( (int)type ).ToString()));

			if ( thRow == null || thRow.Count == 0 )
				return null;

			return GetRepositoryHierarchyChildRepositoryList(
				ItemType.GenericFolder, (int)thRow["child_id"]);
			//return GetRepositoryHierarchyChildRepositoryList(
			//    ItemType.GenericFolder, (int)thRow["child_id"], type);

		}
		/// <summary>
		/// Gets the repository items of domain folder type recursive.
		/// </summary>
		/// <param name="domainId">The domain ID.</param>
		/// <param name="type">The type.</param>
		/// <returns></returns>
		public DataRow[] GetRepositoryItemsOfDomainFolderTypeRecursive(int domainId, ItemType type)
		{
			DataTable dt = GetRepositoryItemsOfDomainFolderType(domainId, type);
			if ( dt == null ) return null;

			List<DataRow> results = new List<DataRow>(dt.Rows.Count);

			getRepositoryItemsOfDomainFolderTypeRecursive(dt, type, ref results);

			return results.ToArray();
		}
		private void getRepositoryItemsOfDomainFolderTypeRecursive(DataTable dtCurrent, ItemType type,
																   ref List<DataRow> results)
		{
			results.AddRange(dtCurrent.Select(string.Format("type = {0}", (int)type)));
			foreach ( DataRow rowFolder in dtCurrent.Select(string.Format("type = {0}",
																		  (int)ItemType.GenericFolder)) ) {
				DataTable dt = GetRepositoryHierarchyChildRepositoryList(
									ItemType.GenericFolder, (int)rowFolder["id"]);
				getRepositoryItemsOfDomainFolderTypeRecursive(dt, type, ref results);
			}
		}

		/// <summary>
		/// Gets the repository item folder of a special item type.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <param name="typeFolder">The type folder.</param>
		/// <returns></returns>
		public GenericFolder GetRepositoryItemFolderOfType(IRepositoryItem item, ItemType typeFolder)
		{
			if ( item == null )
				return null;
			else
				return GetRepositoryItemFolderOfType(item.RepositoryItemType, item.ID, typeFolder);
		}
		/// <summary>
		/// Gets the repository item folder of a special item type.
		/// </summary>
		/// <param name="type">The type.</param>
		/// <param name="id">The ID.</param>
		/// <param name="typeFolder">The type folder.</param>
		/// <returns></returns>
		public GenericFolder GetRepositoryItemFolderOfType(ItemType type, int id, ItemType typeFolder)
		{
			Hashtable thRow = executeRow(
				"SELECT rh.child_id, rh.repository_id " +
				"FROM repository_hierarchy rh " +
				"INNER JOIN generic_folder gf " +
					"ON rh.child_reptype_id = @typeFolder AND gf.folder_id = rh.child_id " +
				"WHERE rh.parent_reptype_id = @type AND rh.parent_id = @id " +
					"AND (gf.folder_types = @folder_types OR " +
					string.Format("PATINDEX('{0};%', gf.folder_types) > 0 OR " +
								  "PATINDEX('%;{0};%', gf.folder_types) > 0 OR " +
								  "PATINDEX('%;{0}', gf.folder_types) > 0 )", ( (int)typeFolder ).ToString()),
				new SqlParameter("@typeFolder", (int)ItemType.GenericFolder),
				new SqlParameter("@type", (int)type),
				new SqlParameter("@id", id),
				new SqlParameter("@folder_types", ( (int)typeFolder ).ToString()));

			if ( thRow == null || thRow.Count == 0 )
				return null;

			return new GenericFolder((int)thRow["child_id"]);
		}

		#region Query UpdateRepositoryHierarchyDisplay
		private const string SQL_UpdateRepositoryHierarchyDisplay =
			"UPDATE repository_hierarchy " +
			"SET parent_display = @display, parent_image_key = @image_key, " +
				"parent_selected_image_key = @selected_image_key " +
			"WHERE parent_reptype_id = @reptype_id AND parent_id = @id; " +
			"UPDATE repository_hierarchy " +
			"SET child_display = @display, child_image_key = @image_key, " +
				"child_selected_image_key = @selected_image_key " +
			"WHERE child_reptype_id = @reptype_id AND child_id = @id";
		#endregion
		public bool UpdateRepositoryHierarchyDisplay(IRepositoryItem item)
		{
			int result = execNonQuery(
				SQL_UpdateRepositoryHierarchyDisplay,
				new SqlParameter("@display", item.Display),
				new SqlParameter("@image_key", item.ImageKey),
				new SqlParameter("@selected_image_key", item.SelectedImageKey),
				new SqlParameter("@reptype_id", item.RepositoryItemType),
				new SqlParameter("@id", item.ID));
			return ( result > 0 );
		}

		#region DeleteRepositoryHierarchy Queries
		private const string _QUERY_DELETE_REPHIER =
			"DELETE FROM repository_hierarchy " +
			" WHERE parent_reptype_id = @parent_reptype_id AND parent_id = @parent_id AND " +
			" child_reptype_id = @child_reptype_id AND child_id = @child_id AND " +
			" repository_id = @repository_id";
		private const string _QUERY_DELETE_REPHIER_PARENTNULL =
			"DELETE FROM repository_hierarchy " +
			" WHERE parent_reptype_id IS NULL AND parent_id IS NULL AND " +
			" child_reptype_id = @child_reptype_id AND child_id = @child_id AND " +
			" repository_id = @repository_id";
		private const string SQL_DeleteRepositoryChildHierarchy =
			"DELETE FROM repository_hierarchy " +
			"WHERE (parent_reptype_id = @type AND parent_id = @id)";
		#endregion
		/// <summary>
		/// Deletes the repository hierarchy.
		/// </summary>
		/// <param name="parentItem">The parent item.</param>
		/// <param name="childItem">The child item.</param>
		/// <param name="repositoryId">The repository ID.</param>
		/// <returns></returns>
		public bool DeleteRepositoryHierarchy(IRepositoryItem parentItem,
			IRepositoryItem childItem, int repositoryId)
		{
			bool deleted = false;
			if ( parentItem != null ) {
				deleted = DeleteRepositoryHierarchy(
					parentItem.RepositoryItemType, parentItem.ID,
					childItem.RepositoryItemType, childItem.ID,
					repositoryId); ;
			} else {
				deleted = DeleteRepositoryHierarchy(
					ItemType.Undefined, -1,
					childItem.RepositoryItemType, childItem.ID,
					repositoryId); ;
			}
			return deleted;
		}
		/// <summary>
		/// Deletes the repository hierarchy.
		/// </summary>
		/// <param name="parentItem">The parent item.</param>
		/// <param name="childItem">The child item.</param>
		/// <returns></returns>
		public bool DeleteRepositoryHierarchy(IRepositoryItem parentItem,
			IRepositoryItem childItem)
		{
			#region Queries
			const string queryDeleteRephier =
				"DELETE FROM repository_hierarchy " +
				" WHERE parent_reptype_id = @parent_reptype_id AND parent_id = @parent_id AND " +
				" child_reptype_id = @child_reptype_id AND child_id = @child_id";
			const string queryDeleteRephierParentNull =
				"DELETE FROM repository_hierarchy " +
				" WHERE parent_reptype_id IS NULL AND parent_id IS NULL AND " +
				" child_reptype_id = @child_reptype_id AND child_id = @child_id";
			#endregion
			int result = 0;
			if ( parentItem != null ) {
				result = execNonQuery(
					queryDeleteRephier,
					new SqlParameter("@parent_reptype_id", parentItem.RepositoryItemType),
					new SqlParameter("@parent_id", parentItem.ID),
					new SqlParameter("@child_reptype_id", childItem.RepositoryItemType),
					new SqlParameter("@child_id", childItem.ID));
			} else {
				result = execNonQuery(
					queryDeleteRephierParentNull,
					new SqlParameter("@child_reptype_id", childItem.RepositoryItemType),
					new SqlParameter("@child_id", childItem.ID));
			}
			bool deleted = ( result > 0 );
			if ( deleted ) {
				notifiyRepositoryChange(parentItem,
					this.GetType().GetMethod("DeleteRepositoryHierarchy",
						new Type[] { typeof(IRepositoryItem), typeof(IRepositoryItem) }),
					null, "Parent");
				notifiyRepositoryChange(childItem,
					this.GetType().GetMethod("DeleteRepositoryHierarchy",
						new Type[] { typeof(IRepositoryItem), typeof(IRepositoryItem) }),
					null, "Child");
			}
			return deleted;
		}
		/// <summary>
		/// Deletes the repository hierarchy.
		/// </summary>
		/// <param name="parentType">Type of the parent.</param>
		/// <param name="parentId">The parent ID.</param>
		/// <param name="childType">Type of the child.</param>
		/// <param name="childId">The child ID.</param>
		/// <param name="repositoryId">The repository ID.</param>
		/// <returns></returns>
		public bool DeleteRepositoryHierarchy(ItemType parentType, int parentId,
			ItemType childType, int childId, int repositoryId)
		{
			int result;
			if ( parentType != ItemType.Undefined && parentId > 0 ) {
				result = execNonQuery(
					_QUERY_DELETE_REPHIER,
					new SqlParameter("@parent_reptype_id", parentType),
					new SqlParameter("@parent_id", parentId),
					new SqlParameter("@child_reptype_id", childType),
					new SqlParameter("@child_id", childId),
					new SqlParameter("@repository_id", repositoryId));
			} else {
				result = execNonQuery(
					_QUERY_DELETE_REPHIER_PARENTNULL,
					new SqlParameter("@child_reptype_id", childType),
					new SqlParameter("@child_id", childId),
					new SqlParameter("@repository_id", repositoryId));
			}
			bool deleted = ( result > 0 );
			if ( deleted ) {
				notifiyRepositoryChange(RepositoryService.GetItem(parentType, parentId, false),
					this.GetType().GetMethod("DeleteRepositoryHierarchy",
						new Type[] { typeof(ItemType), typeof(int), typeof(ItemType), typeof(int), typeof(int) }),
					null, "Parent");
				notifiyRepositoryChange(RepositoryService.GetItem(childType, childId, false),
					this.GetType().GetMethod("DeleteRepositoryHierarchy",
						new Type[] { typeof(ItemType), typeof(int), typeof(ItemType), typeof(int), typeof(int) }),
					null, "Child");
			}
			return deleted;
		}
		/// <summary>
		/// Deletes the repository hierarchy.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <returns></returns>
		public bool DeleteRepositoryHierarchy(IRepositoryItem item)
		{
			return DeleteRepositoryHierarchy(item.RepositoryItemType, item.ID);
		}
		/// <summary>
		/// Deletes the repository hierarchy.
		/// </summary>
		/// <param name="type">The type.</param>
		/// <param name="id">The ID.</param>
		/// <returns></returns>
		public bool DeleteRepositoryHierarchy(ItemType type, int id)
		{
			int result = execNonQuery(
				"DELETE FROM repository_hierarchy " +
				"WHERE (parent_reptype_id = @type AND parent_id = @id) OR " +
				" (child_reptype_id = @type AND child_id = @id)",
				new SqlParameter("@type", (object)( (int)type )),
				new SqlParameter("@id", id));
			bool deleted = ( result > 0 );
			if ( deleted ) {
				// causes error on top level of tree
				if ( type == ItemType.Domain ) return deleted;
				RepositoryService.OnRepositoryChanged(null,
						this.GetType().GetMethod("DeleteRepositoryHierarchy",
						new Type[] { typeof(ItemType), typeof(int) }), null, "All");

			}
			return deleted;
		}
		/// <summary>
		/// Deletes the repository child hierarchy.
		/// </summary>
		/// <param name="item">The item.</param>
		/// <returns></returns>
		public bool DeleteRepositoryChildHierarchy(IRepositoryItem item)
		{
			return DeleteRepositoryChildHierarchy(item.RepositoryItemType, item.ID);
		}
		/// <summary>
		/// Deletes the repository child hierarchy.
		/// </summary>
		/// <param name="type">The type.</param>
		/// <param name="id">The ID.</param>
		/// <returns></returns>
		public bool DeleteRepositoryChildHierarchy(ItemType type, int id)
		{
			int result = execNonQuery(
				SQL_DeleteRepositoryChildHierarchy,
				new SqlParameter("@type", (int)type),
				new SqlParameter("@id", id));
			bool deleted = ( result > 0 );
			if ( deleted ) {
				notifiyRepositoryChange(RepositoryService.GetItem(type, id, false),
					this.GetType().GetMethod("DeleteRepositoryHierarchy",
						new Type[] { typeof(ItemType), typeof(int) }),
					null, "Parent");
			}
			return deleted;
		}
		//public bool DeleteRepositoryChildHierarchyComplete(RepositoryItemBase item)
		//{
		//    return DeleteRepositoryChildHierarchyComplete(item.RepositoryItemType, item.ID);
		//}
		//public bool DeleteRepositoryChildHierarchyComplete(ItemType parentType, int parentId)
		//{
		//    DataTable dt = GetRepositoryHierarchyChildren(parentType, parentId);
		//    foreach ( DataRow row in dt.Rows ) {
		//        DeleteRepositoryChildHierarchyComplete((ItemType)row["child_reptype_id"], (int)row["child_id"]);	
		//    }
		//    int result = execNonQuery(
		//        "DELETE FROM repository_hierarchy " +
		//        "WHERE (parent_reptype_id = @type AND parent_id = @id)",
		//        new SqlParameter("@type", (int)parentType),
		//        new SqlParameter("@id", parentId));
		//    return ( result > 0 );
		//}

		/// <summary>
		/// Existses the repository hierarchy.
		/// </summary>
		/// <param name="itemParent">The item parent.</param>
		/// <param name="itemChild">The item child.</param>
		/// <returns></returns>
		public bool ExistsRepositoryHierarchy(IRepositoryItem itemParent, IRepositoryItem itemChild)
		{
			return ExistsRepositoryHierarchy(itemParent.RepositoryItemType, itemParent.ID,
											 itemChild.RepositoryItemType, itemChild.ID);
		}
		/// <summary>
		/// Existses the repository hierarchy.
		/// </summary>
		/// <param name="typeParent">The type parent.</param>
		/// <param name="idParent">The ID parent.</param>
		/// <param name="typeChild">The type child.</param>
		/// <param name="idChild">The ID child.</param>
		/// <returns></returns>
		public bool ExistsRepositoryHierarchy(ItemType typeParent, int idParent,
											  ItemType typeChild, int idChild)
		{
			int count = (int)execScalar(
				"SELECT Count(*) FROM repository_hierarchy " +
				"WHERE parent_reptype_id = @typeParent AND parent_id = @idParent " +
					"AND child_reptype_id = @typeChild AND child_id = @idChild",
				new SqlParameter("@typeParent", (int)typeParent),
				new SqlParameter("@idParent", idParent),
				new SqlParameter("@typeChild", (int)typeChild),
				new SqlParameter("@idChild", idChild));
			return ( count > 0 );
		}
		/// <summary>
		/// Existses the repository hierarchy.
		/// </summary>
		/// <param name="itemParent">The item parent.</param>
		/// <param name="itemChild">The item child.</param>
		/// <param name="idRepository">The ID repository.</param>
		/// <returns></returns>
		public bool ExistsRepositoryHierarchy(IRepositoryItem itemParent, IRepositoryItem itemChild,
											  int idRepository)
		{
			return ExistsRepositoryHierarchy(itemParent.RepositoryItemType, itemParent.ID,
											 itemChild.RepositoryItemType, itemChild.ID,
											 idRepository);
		}
		/// <summary>
		/// Existses the repository hierarchy.
		/// </summary>
		/// <param name="typeParent">The type parent.</param>
		/// <param name="idParent">The ID parent.</param>
		/// <param name="typeChild">The type child.</param>
		/// <param name="idChild">The ID child.</param>
		/// <param name="idRepository">The ID repository.</param>
		/// <returns></returns>
		public bool ExistsRepositoryHierarchy(ItemType typeParent, int idParent,
											  ItemType typeChild, int idChild,
											  int idRepository)
		{
			int count = (int)execScalar(
				"SELECT Count(*) FROM repository_hierarchy " +
				"WHERE parent_reptype_id = @typeParent AND parent_id = @idParent " +
					"AND child_reptype_id = @typeChild AND child_id = @idChild " +
					"AND repository_id = @idRepository",
				new SqlParameter("@typeParent", (int)typeParent),
				new SqlParameter("@idParent", idParent),
				new SqlParameter("@typeChild", (int)typeChild),
				new SqlParameter("@idChild", idChild),
				new SqlParameter("@idRepository", idRepository));

			return ( count > 0 );
		}

		private string getTableName(ItemType itemtype)
		{
			string tblnm = "";
			switch ( itemtype ) {
				case ItemType.Domain:
					tblnm = "domains";
					break;
				//case ItemType.NounFolder:
				//case ItemType.NounCategory:
				case ItemType.Noun:
					tblnm = "nouns";
					break;
				//case ItemType.ServiceFolder:
				case ItemType.Component:
					tblnm = "services";
					break;
				//case ItemType.OperationFolder:
				case ItemType.Operation:
					tblnm = "operations";
					break;
				//case ItemType.BRuleFolder:
				case ItemType.BRule:
					tblnm = "repository_business_rules";
					break;
				//case ItemType.BObjectFolder:
				case ItemType.BObject:
					tblnm = "business_objects";
					break;
				//case ItemType.VerbFolder:
				case ItemType.Verb:
					tblnm = "verbs";
					break;
				case ItemType.Repository:
					tblnm = "repositories";
					break;
				case ItemType.GenericFolder:
					tblnm = "generic_folder";
					break;
				case ItemType.BPolicy:
					tblnm = "business_policies";
					break;
				case ItemType.Composite:
					tblnm = "service_modules";
					break;
				case ItemType.Diagram:
					tblnm = "repository_diagrams";
					break;
				case ItemType.System:
					tblnm = "systems";
					break;
				case ItemType.MessageQueue:
				case ItemType.WebService:
					tblnm = "webservices";
					break;
				case ItemType.WebMethod:
					tblnm = "webmethods";
					break;
				case ItemType.Workflow:
					tblnm = "workflows";
					break;
				case ItemType.SOBA:
					tblnm = "sobas";
					break;
				case ItemType.KPI:
					tblnm = "key_performance_indicators";
					break;
				case ItemType.KPI_Function:
					tblnm = "kpi_functions";
					break;
				case ItemType.KPI_Metric:
					tblnm = "kpi_metrics";
					break;
				case ItemType.KPI_Schedule:
					tblnm = "kpi_schedule";
					break;
				case ItemType.LogicalActivity:
					tblnm = "logical_activities";
					break;
				case ItemType.RuleConstant:
					tblnm = "rule_constants";
					break;
				case ItemType.HumanActivity:
					tblnm = "human_activities";
					break;
				case ItemType.Workflow_Method:
					tblnm = "wf_methods";
					break;
				case ItemType.Workflow_Event:
					tblnm = "wf_events";
					break;
				case ItemType.Workflow_Field:
					tblnm = "wf_fields";
					break;
				case ItemType.LogicalWebMethod:
					tblnm = "logical_webmethods";
					break;
				case ItemType.Capability:
					tblnm = "capabilities";
					break;
				case ItemType.LogicalMethodGroup:
					tblnm = "logical_method_groups";
					break;
				case ItemType.Organization:
					tblnm = "organization";
					break;
				case ItemType.OrgUnit:
					tblnm = "organization_unit";
					break;
				case ItemType.Role:
					tblnm = "role";
					break;
				case ItemType.User:
					tblnm = "users";
					break;
				case ItemType.Documentation:
					tblnm = "documentations";
					break;
				default:
					throw new Exception("No database table exists for this item type.");
			}
			return tblnm;
		}
		private string getIDColumnName(ItemType itemtype)
		{
			string idcolnm = "";
			switch ( itemtype ) {
				case ItemType.Domain:
					idcolnm = "domain_id";
					break;
				//case ItemType.NounFolder:
				//case ItemType.NounCategory:
				case ItemType.Noun:
					idcolnm = "noun_id";
					break;
				//case ItemType.ServiceFolder:
				case ItemType.Component:
					idcolnm = "service_id";
					break;
				//case ItemType.OperationFolder:
				case ItemType.Operation:
					idcolnm = "operation_id";
					break;
				//case ItemType.BRuleFolder:
				case ItemType.BRule:
					idcolnm = "brule_id";
					break;
				//case ItemType.BObjectFolder:
				case ItemType.BObject:
					idcolnm = "bobj_id";
					break;
				//case ItemType.VerbFolder:
				case ItemType.Verb:
					idcolnm = "verb_id";
					break;
				case ItemType.Repository:
					idcolnm = "repository_id";
					break;
				case ItemType.GenericFolder:
					idcolnm = "folder_id";
					break;
				case ItemType.BPolicy:
					idcolnm = "bpolicy_id";
					break;
				case ItemType.Composite:
					idcolnm = "svcmodule_id";
					break;
				case ItemType.Diagram:
					idcolnm = "diagram_id";
					break;
				case ItemType.System:
					idcolnm = "system_id";
					break;
				case ItemType.MessageQueue:
				case ItemType.WebService:
					idcolnm = "websvc_id";
					break;
				case ItemType.WebMethod:
					idcolnm = "webmethod_id";
					break;
				case ItemType.Workflow:
					idcolnm = "workflow_id";
					break;
				case ItemType.SOBA:
					idcolnm = "soba_id";
					break;
				case ItemType.KPI:
					idcolnm = "kpi_id";
					break;
				case ItemType.KPI_Function:
					idcolnm = "function_id";
					break;
				case ItemType.KPI_Metric:
					idcolnm = "metric_id";
					break;
				case ItemType.KPI_Schedule:
					idcolnm = "schedule_id";
					break;
				case ItemType.LogicalActivity:
					idcolnm = "log_act_id";
					break;
				case ItemType.RuleConstant:
					idcolnm = "ruleconst_id";
					break;
				case ItemType.HumanActivity:
					idcolnm = "hact_id";
					break;
				case ItemType.Workflow_Method:
					idcolnm = "wf_method_id";
					break;
				case ItemType.Workflow_Event:
					idcolnm = "wf_event_id";
					break;
				case ItemType.Workflow_Field:
					idcolnm = "wf_field_id";
					break;
				case ItemType.LogicalWebMethod:
					idcolnm = "log_wmeth_id";
					break;
				case ItemType.Capability:
					idcolnm = "capability_id";
					break;
				case ItemType.LogicalMethodGroup:
					idcolnm = "lm_group_id";
					break;
				case ItemType.Organization:
					idcolnm = "org_id";
					break;
				case ItemType.OrgUnit:
					idcolnm = "org_unit_id";
					break;
				case ItemType.Role:
					idcolnm = "role_id";
					break;
				case ItemType.User:
					idcolnm = "user_id";
					break;
				case ItemType.Documentation:
					idcolnm = "doc_id";
					break;
				default:
					throw new Exception("No database table exists for this item type.");
			}
			return idcolnm;
		}
		private bool hasDeletedColumn(ItemType itemtype)
		{
			switch ( itemtype ) {
				case ItemType.Domain:
				//case ItemType.NounFolder:
				//case ItemType.NounCategory:
				case ItemType.Noun:
				//case ItemType.ServiceFolder:
				case ItemType.Component:
				//case ItemType.OperationFolder:
				case ItemType.Operation:
				//case ItemType.BObjectFolder:
				case ItemType.BObject:
				//case ItemType.VerbFolder:
				case ItemType.Verb:
				case ItemType.Repository:
				case ItemType.BPolicy:
				case ItemType.Diagram:
				case ItemType.Composite:
				case ItemType.BRule:
				case ItemType.GenericFolder:
				case ItemType.System:
				case ItemType.WebService:
				case ItemType.WebMethod:
				case ItemType.Workflow:
				case ItemType.SOBA:
				case ItemType.LogicalActivity:
				case ItemType.RuleConstant:
				case ItemType.HumanActivity:
				case ItemType.LogicalWebMethod:
				case ItemType.Capability:
				case ItemType.LogicalMethodGroup:
				case ItemType.Documentation:
					return true;
				default:
					return false;
			}
		}

		/// <summary>
		/// Searches the repository items.
		/// </summary>
		/// <param name="domainId">The domain ID.</param>
		/// <param name="name">The name.</param>
		/// <param name="desc">The desc.</param>
		/// <param name="searchExact">if set to <c>true</c> [search exact].</param>
		/// <returns></returns>
		public DataTable SearchRepositoryItems(
			int domainId, string name, string desc, bool searchExact)
		{
			//PROCEDURE search_repository_items 
			//    @org_id int,
			//    @domain_id int,
			//    @name nvarchar(250),
			//    @desc nvarchar(1000) = NULL,
			//    @search_exact bit = 1

			List<SqlParameter> sqlparams = new List<SqlParameter>();
			sqlparams.Add(new SqlParameter("@org_id", Global.CurrentUser.OrganizationID));
			sqlparams.Add(new SqlParameter("@domain_id",
				( domainId > 0 ? (object)domainId : (object)DBNull.Value )));
			sqlparams.Add(new SqlParameter("@name",
				( string.IsNullOrEmpty(name) ? (object)DBNull.Value : name )));
			sqlparams.Add(new SqlParameter("@desc",
				( string.IsNullOrEmpty(desc) ? (object)DBNull.Value : desc )));
			sqlparams.Add(new SqlParameter("@search_exact", searchExact));

			DataSet ds = execProcedure("search_repository_items", sqlparams.ToArray());
			if ( ds.Tables.Count > 0 ) {
				return ds.Tables[0];
			} else {
				return null;
			}
		}
		/// <summary>
		/// Searches the repository items.
		/// </summary>
		/// <param name="domainId">The domain ID.</param>
		/// <param name="type">The type.</param>
		/// <param name="name">The name.</param>
		/// <param name="desc">The desc.</param>
		/// <param name="searchExact">if set to <c>true</c> [search exact].</param>
		/// <returns></returns>
		public DataTable SearchRepositoryItems(
			int domainId, ItemType type, string name, string desc, bool searchExact)
		{
			//PROCEDURE search_repository_items 
			//    @org_id int,
			//    @domain_id int,
			//    @name nvarchar(250),
			//    @desc nvarchar(1000) = NULL,
			//	  @type int = NULL,
			//    @search_exact bit = 1

			List<SqlParameter> sqlparams = new List<SqlParameter>();
			sqlparams.Add(new SqlParameter("@org_id", Global.CurrentUser.OrganizationID));
			sqlparams.Add(new SqlParameter("@domain_id",
				( domainId > 0 ? (object)domainId : (object)DBNull.Value )));
			sqlparams.Add(new SqlParameter("@name",
				( string.IsNullOrEmpty(name) ? (object)DBNull.Value : name )));
			sqlparams.Add(new SqlParameter("@desc",
				( string.IsNullOrEmpty(desc) ? (object)DBNull.Value : desc )));
			sqlparams.Add(new SqlParameter("@type",
				( type == ItemType.Undefined ? (object)DBNull.Value : (object)(int)type )));
			sqlparams.Add(new SqlParameter("@search_exact", searchExact));

			DataSet ds = execProcedure("search_repository_items", sqlparams.ToArray());
			if ( ds.Tables.Count > 0 ) {
				return ds.Tables[0];
			} else {
				return null;
			}
		}
		/// <summary>
		/// Searches the repository items.
		/// </summary>
		/// <param name="type">The type.</param>
		/// <param name="columnName">Name of the column.</param>
		/// <param name="value">The value.</param>
		/// <param name="useWildcards">if set to <c>true</c> [use wildcards].</param>
		/// <returns></returns>
		public DataTable SearchRepositoryItems(ItemType type, string columnName, string value, bool useWildcards)
		{
			string tbl = getTableName(type);
			string query = string.Format(
				"SELECT * FROM {0} WHERE {1} {2} @val AND org_id = @org_id",
				tbl, columnName, ( useWildcards ? "LIKE" : "=" ));

			return fillDataTable(query, new SqlParameter("@val", value),
										new SqlParameter("@org_id", Global.CurrentUser.OrganizationID));
		}

		#endregion

		#region Component / Opeations <=> WebService / WebMethod - Connections

		#region Service-WebService SQL Queries
		private const string SQL_SetServiceWebService =
			"IF NOT EXISTS(SELECT * FROM service_webservice " +
			" WHERE service_id = @service_id AND websvc_id = @websvc_id) " +
			"BEGIN " +
				"INSERT INTO service_webservice (service_id, websvc_id) " +
				"VALUES (@service_id, @websvc_id) " +
			"END";
		private const string SQL_DeleteServiceWebService =
			"DELETE FROM service_webservice " +
			"WHERE service_id = @service_id AND websvc_id = @websvc_id";
		//private static string SQL_DeleteServiceWebServices =
		//    "DELETE FROM service_webservice " +
		//    "WHERE service_id = @service_id AND websvc_id = @websvc_id";
		private const string SQL_DeleteWebServiceServices =
			"DELETE FROM service_webservice " +
			"WHERE websvc_id = @websvc_id";
		private const string SQL_GetServiceWebServices =
			"SELECT * FROM webservices ws " +
			"INNER JOIN service_webservice sws ON sws.websvc_id = ws.websvc_id " +
			"WHERE sws.service_id = @service_id";
		#endregion

		public bool SetServiceWebService(int idService, int idWebService)
		{
			int count = execNonQuery(SQL_SetServiceWebService,
				new SqlParameter("@service_id", idService),
				new SqlParameter("@websvc_id", idWebService));
			return ( count > 0 );
		}
		public bool DeleteServiceWebService(int idService, int idWebService)
		{
			int count = execNonQuery(SQL_DeleteServiceWebService,
				new SqlParameter("@service_id", idService),
				new SqlParameter("@websvc_id", idWebService));
			return ( count > 0 );
		}
		public bool RemoveWebServiceServices(int idWebService)
		{
			int count = execNonQuery(SQL_DeleteWebServiceServices,
				new SqlParameter("@websvc_id", idWebService));
			return ( count > 0 );
		}
		public BindingListEx<WebService> GetServiceWebService(int idService)
		{
			BindingListEx<WebService> listWS = new BindingListEx<WebService>();
			DataTable dt = fillDataTable(SQL_GetServiceWebServices,
				new SqlParameter("@service_id", idService));

			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					WebService ws = RepositoryService.GetItem(ItemType.WebService, row) as WebService;
					if ( ws != null )
						listWS.Add(ws);
				}
			}
			return listWS;
		}

		#region Operation-WebMethod SQL Queries
		private const string SQL_SetOperationWebMethod =
			"IF NOT EXISTS(SELECT * FROM operation_webmethod " +
			" WHERE operation_id = @operation_id AND webmethod_id = @webmethod_id) " +
			"BEGIN " +
				"INSERT INTO operation_webmethod " +
				" (operation_id, webmethod_id, input_connections, input_xslt, output_connections, output_xslt) " +
				"VALUES " +
				" (@operation_id, @webmethod_id, @input_connections, @input_xslt, @output_connections, @output_xslt) " +
			"END ELSE BEGIN " +
				"UPDATE operation_webmethod SET " +
				" input_connections = @input_connections, input_xslt = @input_xslt, " +
				" output_connections = @output_connections, output_xslt = @output_xslt " +
				"WHERE operation_id = @operation_id AND webmethod_id = @webmethod_id " +
			"END";
		private const string SQL_DeleteOperationWebMethod =
			"DELETE FROM operation_webmethod " +
			"WHERE operation_id = @operation_id AND webmethod_id = @webmethod_id";
		private const string SQL_DeleteOperationWebMethods =
			"DELETE FROM operation_webmethod WHERE operation_id = @operation_id";
		private const string SQL_DeleteWebMethodOperations =
			"DELETE FROM operation_webmethod " +
			"WHERE webmethod_id = @webmethod_id";
		private const string SQL_GetOperationWebMethods =
			"SELECT * FROM webmethods wm " +
			"INNER JOIN operation_webmethod owm ON owm.webmethod_id = wm.webmethod_id " +
			"WHERE owm.operation_id = @operation_id";
		private const string SQL_GetOperationWebMethodDetails =
			"SELECT * FROM operation_webmethod " +
			"WHERE operation_id = @operation_id AND webmethod_id = @webmethod_id";
		#endregion

		public bool SetOperationWebMethod(int idOperation, int idWebMethod,
			XmlDocument inputConnections, XsltDocument inputXSLT,
			XmlDocument outputConnections, XsltDocument outputXSLT)
		{
			int count = execNonQuery(SQL_SetOperationWebMethod,
				new SqlParameter("@operation_id", idOperation),
				new SqlParameter("@webmethod_id", idWebMethod),
				new SqlParameter("@input_connections", inputConnections == null ? (object)DBNull.Value : (object)inputConnections.DocumentElement.OuterXml),
				new SqlParameter("@input_xslt", inputXSLT == null ? (object)DBNull.Value : (object)inputXSLT.DocumentElement.OuterXml),
				new SqlParameter("@output_connections", outputConnections == null ? (object)DBNull.Value : (object)outputConnections.DocumentElement.OuterXml),
				new SqlParameter("@output_xslt", outputXSLT == null ? (object)DBNull.Value : (object)outputXSLT.DocumentElement.OuterXml));
			return ( count > 0 );
		}
		public bool DeleteOperationWebMethod(int idOperation, int idWebMethod)
		{
			int count = execNonQuery(SQL_DeleteOperationWebMethod,
				new SqlParameter("@operation_id", idOperation),
				new SqlParameter("@webmethod_id", idWebMethod));
			return ( count > 0 );
		}
		public void DeleteOperationWebMethods(int idOperation)
		{
			try {
				execNonQuery(SQL_DeleteOperationWebMethods,
					new SqlParameter("@operation_id", idOperation));
			} catch { }
		}
		public bool RemoveWebMethodOperations(int idWebMethod)
		{
			int count = execNonQuery(SQL_DeleteWebMethodOperations,
				new SqlParameter("@webmethod_id", idWebMethod));
			return ( count > 0 );
		}
		public BindingListEx<WebMethod> GetOperationWebMethods(int idOperation)
		{
			BindingListEx<WebMethod> listWM = new BindingListEx<WebMethod>();
			DataTable dt = fillDataTable(SQL_GetOperationWebMethods,
				new SqlParameter("@operation_id", idOperation));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					WebMethod wm = RepositoryService.GetItem(ItemType.WebMethod, row) as WebMethod;
					if ( wm != null )
						listWM.Add(wm);
				}
			}
			return listWM;
		}
		public bool GetOperationWebMethodDetails(int idOperation, int idWebMethod,
			out XmlDocument inputConnections, out XsltDocument inputXSLT,
			out XmlDocument outputConnections, out XsltDocument outputXSLT)
		{
			inputConnections = null;
			inputXSLT = null;
			outputConnections = null;
			outputXSLT = null;
			DataTable dt = fillDataTable(SQL_GetOperationWebMethodDetails,
				new SqlParameter("@operation_id", idOperation),
				new SqlParameter("@webmethod_id", idWebMethod));
			if ( dt != null && dt.Rows.Count > 0 ) {
				DataRow row = dt.Rows[0];
				if ( !row.IsNull("input_connections") &&
					 !string.IsNullOrEmpty(row["input_connections"].ToString()) ) {
					inputConnections = new XmlDocument();
					inputConnections.LoadXml(row["input_connections"].ToString());
				}
				if ( !row.IsNull("input_xslt") &&
					 !string.IsNullOrEmpty(row["input_xslt"].ToString()) ) {
					inputXSLT = new XsltDocument();
					inputXSLT.LoadXml(row["input_xslt"].ToString());
				}
				if ( !row.IsNull("output_connections") &&
					 !string.IsNullOrEmpty(row["output_connections"].ToString()) ) {
					outputConnections = new XmlDocument();
					outputConnections.LoadXml(row["output_connections"].ToString());
				}
				if ( !row.IsNull("output_xslt") &&
					 !string.IsNullOrEmpty(row["output_xslt"].ToString()) ) {
					outputXSLT = new XsltDocument();
					outputXSLT.LoadXml(row["output_xslt"].ToString());
				}
			}
			return true;
		}

		#endregion

		#region Operation - Workflow - Connection

		#region Operation-Workflow SQL Queries
		private const string SQL_GetOperationWorkflow =
			"SELECT w.* FROM workflows w " +
			"INNER JOIN operations o ON o.operation_workflow_id = w.workflow_id " +
			"WHERE o.operation_id = @operation_id";
		private const string SQL_GetWorkflowOperation =
			"SELECT o.* FROM operations o " +
			"WHERE o.operation_workflow_id = @workflow_id AND o.is_deleted = 0";
		#endregion

		public BindingListEx<Repository.Workflow> GetOperationWorkflow(int idOperation)
		{
			BindingListEx<Repository.Workflow> listWF = new BindingListEx<Repository.Workflow>();
			DataTable dt = fillDataTable(SQL_GetOperationWorkflow,
				new SqlParameter("@operation_id", idOperation));

			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					Repository.Workflow wf = RepositoryService.GetItem(ItemType.Workflow, row) as Repository.Workflow;
					if ( wf != null )
						listWF.Add(wf);
				}
			}
			return listWF;
		}
		public BindingListEx<Repository.Operation> GetWorkflowOperation(int idWorkflow)
		{
			BindingListEx<Repository.Operation> listOp = new BindingListEx<Repository.Operation>();
			DataTable dt = fillDataTable(SQL_GetWorkflowOperation,
				new SqlParameter("@workflow_id", idWorkflow));

			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					Repository.Operation op = RepositoryService.GetItem(ItemType.Operation, row) as Repository.Operation;
					if ( op != null )
						listOp.Add(op);
				}
			}
			return listOp;
		}

		#endregion

		#region GetSOBAofWorkflow
		private const string SQL_GetSOBAofWorkflow =
			"SELECT * FROM sobas WHERE workflow_id = @workflow_id AND is_deleted != 1";
		public SOBA GetSOBAofWorkflow(int idWorkflow)
		{
			SOBA soba = null;
			DataTable dt = fillDataTable(SQL_GetSOBAofWorkflow, new SqlParameter("@workflow_id", idWorkflow));
			if ( dt != null && dt.Rows.Count > 0 ) {
				soba = RepositoryService.GetItem(ItemType.SOBA, dt.Rows[0]) as SOBA;
			}
			return soba;
		}
		#endregion

		#region GetServiceModuleOfWorkflow
		private const string SQL_GetServiceModuleOfWorkflow =
			"SELECT * FROM service_modules WHERE workflow_id = @workflow_id AND is_deleted != 1";
		public ServiceModule GetServiceModuleOfWorkflow(int idWorkflow)
		{
			ServiceModule svcmodule = null;
			DataTable dt = fillDataTable(SQL_GetServiceModuleOfWorkflow,
										 new SqlParameter("@workflow_id", idWorkflow));
			if ( dt != null && dt.Rows.Count > 0 ) {
				svcmodule = RepositoryService.GetItem<ServiceModule>((int)dt.Rows[0]["svcmodule_id"]);
			}
			return svcmodule;
		}
		#endregion

		#region Logical Activity - Business Objects, Verbs, Operations, WorkflowMethods, WorkflowEvents, Bindings

		#region queries
		private const string SQL_GetLogicalActivityBusinessObjects =
			"SELECT bo.bobj_id, labo.published_log_act_id FROM business_objects bo " +
			"INNER JOIN log_act_bobjects labo ON labo.bobj_id = bo.bobj_id " +
			"WHERE labo.log_act_id = @log_act_id AND is_subscription = @is_subscription AND bo.is_deleted = 0";
		private const string SQL_AssignBusinessObjectToLogicalActivity =
			"IF NOT EXISTS (SELECT * FROM log_act_bobjects " +
				"WHERE log_act_id = @log_act_id AND bobj_id = @bobj_id AND is_subscription = @is_subscription AND published_log_act_id = @published_log_act_id) " +
			"BEGIN " +
				"INSERT INTO log_act_bobjects (log_act_id, bobj_id, is_subscription, published_log_act_id) " +
				"VALUES (@log_act_id, @bobj_id, @is_subscription, @published_log_act_id) " +
			"END";
		private const string SQL_DeleteBusinessObjectFromLogicalActivity =
			"DELETE FROM log_act_bobjects " +
			"WHERE log_act_id = @log_act_id AND bobj_id = @bobj_id AND is_subscription = @is_subscription AND published_log_act_id = @published_logact";
		private const string SQL_DeleteAllBusinessObjectsFromLogicalActivity =
			"DELETE FROM log_act_bobjects " +
			"WHERE log_act_id = @log_act_id AND is_subscription = @is_subscription";
		private const string SQL_GetLogicalActivitiesByOperation =
			"SELECT * FROM logical_activities " +
			"WHERE log_act_operation_id = @operation_id AND workflow_id IS NOT NULL AND is_deleted = 0";
		private const string SQL_GetLogicalActivitiesBy =
			"SELECT * FROM logical_activities " +
			"WHERE log_act_{0} = @{0} AND is_deleted = 0";
		#endregion

		#region methods
		public NamedBusinessObjectCollection GetLogicalActivityBusinessObjects(int idLogicalActivity, bool isSubscription)
		{
			NamedBusinessObjectCollection lstBObjects = new NamedBusinessObjectCollection();
			List<Hashtable> rows = executeRows(
				SQL_GetLogicalActivityBusinessObjects,
				new SqlParameter("@log_act_id", idLogicalActivity),
				new SqlParameter("@is_subscription", isSubscription));

			foreach ( Hashtable row in rows ) {
				object boId = row["bobj_id"];
				object logActId = row["published_log_act_id"];
				BusinessObject bo = RepositoryService.GetItem<BusinessObject>(boId == null ? -1 : (int)boId);
				LogicalActivity la = RepositoryService.GetItem<LogicalActivity>(logActId == DBNull.Value ? -1 : (int)logActId);
				if ( bo != null ) {
					string name = bo.Name;
					if ( la == null && bo.WorkflowFieldID > 0 ) {
						name = bo.WorkflowField.Name;
					} else if ( la != null ) {
						name = la.OutputVariableName;
					}
					lstBObjects.Add(name, bo, la);
				}
			}
			//DataTable dt = fillDataTable(
			//    SQL_GetLogicalActivityBusinessObjects,
			//    new SqlParameter("@log_act_id", idLogicalActivity),
			//    new SqlParameter("@is_subscription", isSubscription));
			//if ( dt != null && dt.Rows.Count > 0 ) {
			//    foreach ( DataRow row in dt.Rows ) {
			//        BusinessObject bo = this.RepositoryService.GetItem(ItemType.BObject, row) as BusinessObject;
			//        if ( bo != null )
			//            lstBObjects.Add(bo);
			//    }
			//}
			return lstBObjects;
		}
		public bool AddBusinessObjectToLogicalActivity(int idLogicalActivity, int idBusinessObject, bool isSubscription, int publishedlogAct)
		{
			execNonQuery(SQL_AssignBusinessObjectToLogicalActivity,
				new SqlParameter("@log_act_id", idLogicalActivity),
				new SqlParameter("@bobj_id", idBusinessObject),
				new SqlParameter("@is_subscription", isSubscription),
				new SqlParameter("@published_log_act_id", publishedlogAct));
			return true;
		}
		public bool RemoveBusinessObjectFromLogicalActivity(int idLogicalActivity, int idBusinessObject, bool isSubscription, int publishedlogAct)
		{
			execNonQuery(SQL_DeleteBusinessObjectFromLogicalActivity,
				new SqlParameter("@log_act_id", idLogicalActivity),
				new SqlParameter("@bobj_id", idBusinessObject),
				new SqlParameter("@is_subscription", isSubscription),
				new SqlParameter("@published_logact", publishedlogAct));
			return true;
		}
		public void RemoveAllBusinessObjectsFromLogicalActivity(int idLogicalActivity, bool isSubscription)
		{
			execNonQuery(SQL_DeleteAllBusinessObjectsFromLogicalActivity,
				new SqlParameter("@log_act_id", idLogicalActivity),
				new SqlParameter("@is_subscription", isSubscription));
		}

		public BindingListEx<LogicalActivity> GetLogicalActivitiesByOperation(int idOperation)
		{
			BindingListEx<LogicalActivity> lstLogActs = new BindingListEx<LogicalActivity>();
			DataTable dt = fillDataTable(
				SQL_GetLogicalActivitiesByOperation,
				new SqlParameter("@operation_id", idOperation));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					LogicalActivity logact = this.RepositoryService.GetItem(ItemType.LogicalActivity, row) as LogicalActivity;
					if ( logact != null )
						lstLogActs.Add(logact);
				}
			}
			return lstLogActs;
		}
		public BindingListEx<LogicalActivity> GetLogicalActivitiesByWorkflowMethod(int idWorkflowMethod)
		{
			BindingListEx<LogicalActivity> lstLogActs = new BindingListEx<LogicalActivity>();
			DataTable dt = fillDataTable(
				string.Format(SQL_GetLogicalActivitiesBy, "wf_method_id"),
				new SqlParameter("@wf_method_id", idWorkflowMethod));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					LogicalActivity logact = this.RepositoryService.GetItem(ItemType.LogicalActivity, row) as LogicalActivity;
					if ( logact != null )
						lstLogActs.Add(logact);
				}
			}
			return lstLogActs;
		}
		public BindingListEx<LogicalActivity> GetLogicalActivitiesByWorkflowEvent(int idWorkflowEvent)
		{
			BindingListEx<LogicalActivity> lstLogActs = new BindingListEx<LogicalActivity>();
			DataTable dt = fillDataTable(
				string.Format(SQL_GetLogicalActivitiesBy, "wf_event_id"),
				new SqlParameter("@wf_event_id", idWorkflowEvent));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					LogicalActivity logact = this.RepositoryService.GetItem(ItemType.LogicalActivity, row) as LogicalActivity;
					if ( logact != null )
						lstLogActs.Add(logact);
				}
			}
			return lstLogActs;
		}

		public void SetLogicalActivityBinding(LogicalActivityBinding binding)
		{
			const string query =
				"IF EXISTS ( SELECT * FROM logicalactivity_bindings " +
				" WHERE log_act_id = @log_act_id AND websvc_id = @websvc_id AND webmethod_id = @webmethod_id ) " +
				"BEGIN " +
					"UPDATE logicalactivity_bindings SET " +
					" hact_id = @hact_id, " +
					" input_connections = @input_connections, input_xslt = @input_xslt, " +
					" output_connections = @output_connections, output_xslt = @output_xslt, " +
					" properties = @properties " +
					"WHERE log_act_id = @log_act_id AND websvc_id = @websvc_id AND " +
					" webmethod_id = @webmethod_id " +
				"END ELSE BEGIN " +
					"INSERT INTO logicalactivity_bindings " +
					" (log_act_id, websvc_id, webmethod_id, hact_id, input_connections, input_xslt, " +
					"  output_connections, output_xslt, properties) VALUES " +
					" (@log_act_id, @websvc_id, @webmethod_id, @hact_id, @input_connections, @input_xslt, " +
					"  @output_connections, @output_xslt, @properties) " +
				"END";

			string props = xmlSerializeObject(binding.ExtendedProperties);
			execNonQuery(query,
				new SqlParameter("@log_act_id", (object)binding.LogicalActivityID),
				new SqlParameter("@websvc_id", (object)binding.WebServiceID),
				new SqlParameter("@webmethod_id", (object)binding.WebMethodID),
				new SqlParameter("@hact_id", binding.HumanActivityID > 0 ? (object)binding.HumanActivityID : (object)DBNull.Value),
				new SqlParameter("@input_connections", binding.InputConnections ?? (object)DBNull.Value),
				new SqlParameter("@input_xslt", binding.InputXslt != null ? binding.InputXslt.DocumentElement.OuterXml : (object)DBNull.Value),
				new SqlParameter("@output_connections", binding.OutputConnections ?? (object)DBNull.Value),
				new SqlParameter("@output_xslt", binding.OutputXslt != null ? binding.OutputXslt.DocumentElement.OuterXml : (object)DBNull.Value),
				new SqlParameter("@properties", props ?? (object)DBNull.Value));
		}
		public void DeleteLogicalActivityBinding(int logactID, int webserviceID, int webmethodID)
		{
			const string query =
				"DELETE FROM logicalactivity_bindings " +
				"WHERE log_act_id = @log_act_id AND websvc_id = @websvc_id AND webmethod_id = @webmethod_id";
			execNonQuery(query,
				new SqlParameter("@log_act_id", logactID),
				new SqlParameter("@websvc_id", webserviceID),
				new SqlParameter("@webmethod_id", webmethodID));
		}
		public BindingListEx<LogicalActivityBinding> GetLogicalActivityBindings(
			LogicalActivity logact)
		{
			const string query =
				"SELECT * FROM logicalactivity_bindings WHERE log_act_id = @log_act_id";

			BindingListEx<LogicalActivityBinding> lstBindings = new BindingListEx<LogicalActivityBinding>();
			DataTable dt = fillDataTable(query,
				new SqlParameter("@log_act_id", (object)logact.ID));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					LogicalActivityBinding lab = new LogicalActivityBinding(logact);
					lab.LogicalActivityID = (int)row["log_act_id"];
					lab.WebServiceID = (int)row["websvc_id"];
					lab.WebMethodID = (int)row["webmethod_id"];
					lab.HumanActivityID = row.IsNull("hact_id") ? -1 : (int)row["hact_id"];
					if ( !row.IsNull("input_connections") )
						lab.InputConnections = (string)row["input_connections"];
					if ( !row.IsNull("input_xslt") ) {
						lab.InputXslt = new XsltDocument();
						lab.InputXslt.LoadXml((string)row["input_xslt"]);
					}
					if ( !row.IsNull("output_connections") )
						lab.OutputConnections = (string)row["output_connections"];
					if ( !row.IsNull("output_xslt") ) {
						lab.OutputXslt = new XsltDocument();
						lab.OutputXslt.LoadXml((string)row["output_xslt"]);
					}
					if ( !row.IsNull("properties") ) {
						lab.ExtendedProperties =
							xmlDeserializeObject<LogicalActivity.LogicalActivityProperties>(row["properties"].ToString());
					}
					if ( !lstBindings.Contains(lab) )
						lstBindings.Add(lab);
				}
			}
			return lstBindings;
		}

		public BindingListEx<LogicalActivity> GetLogicalActivitiesOfNoun(int nounID)
		{
			string query =
				"DECLARE @countold int" + Environment.NewLine +
				"DECLARE @countnew int" + Environment.NewLine +

				"CREATE TABLE #bos ( bobj_id int )" + Environment.NewLine +

				"INSERT INTO #bos" + Environment.NewLine +
				"SELECT bobj_id FROM bobj_items WHERE item_noun_id = @noun_id" + Environment.NewLine +

				"SET @countold = -1" + Environment.NewLine +
				"SET @countnew = 0" + Environment.NewLine +

				"WHILE @countold != @countnew" + Environment.NewLine +
				"BEGIN" + Environment.NewLine +

					"SELECT @countold = count(*) FROM #bos" + Environment.NewLine +

					"INSERT INTO #bos" + Environment.NewLine +
					"SELECT	bobj_id FROM bobj_items" + Environment.NewLine +
					"WHERE	item_bobj_id IN ( SELECT bobj_id FROM #bos ) AND" + Environment.NewLine +
					"		bobj_id NOT IN ( SELECT bobj_id FROM #bos )" + Environment.NewLine +

					"SELECT @countnew = count(*) FROM #bos" + Environment.NewLine +
				"END" + Environment.NewLine +

				"SELECT DISTINCT logical_activities.log_act_id FROM logical_activities" + Environment.NewLine +
				"INNER JOIN log_act_bobjects ON logical_activities.log_act_id = log_act_bobjects.log_act_id" + Environment.NewLine +
				"WHERE bobj_id IN ( SELECT bobj_id FROM #bos ) AND logical_activities.is_deleted = 0" + Environment.NewLine +

				"DROP TABLE #bos";

			DataTable dt = fillDataTable(query, new SqlParameter("@noun_id", (object)nounID));

			return fillSSBList<LogicalActivity>(dt);
		}

		public BindingListEx<LogicalActivity> GetLogicalActivitiesOfVerb(int verbID)
		{
			const string query =
				"SELECT la.* FROM verbs v, logical_activities la, workflows wf " +
				"WHERE v.verb_id = @verb_id AND v.verb_id = la.log_act_verb_id AND " +
					"la.workflow_id = wf.workflow_id AND " +
					"wf.is_deleted = 0 AND v.is_deleted = 0 AND la.is_deleted = 0";

			DataTable dt = fillDataTable(query, new SqlParameter("@verb_id", (object)verbID));

			return fillSSBList<LogicalActivity>(dt);
		}

		#endregion

		#endregion

		#region Logical Web Methods - Web Methods, Logical Activities, HumanActivities, LogicalRuleResultEndpoint
		public LogicalWebMethod GetLogicalWebMethodOfWebMethod(int webMethodID)
		{
			const string query =
				"SELECT log_wmeth_id FROM logical_webmethods " +
				"WHERE webmethod_id = @webmethod_id AND is_deleted = 0";

			LogicalWebMethod logwmeth = null;

			object obj = execScalar(query, new SqlParameter("@webmethod_id", (object)webMethodID));
			if ( obj is int ) {
				logwmeth = this.RepositoryService.GetItem<LogicalWebMethod>((int)obj);
			} else if ( obj == null ) {
				const string query2 =
					"SELECT lrw.log_wmeth_id FROM logmeth_rule_webmethods AS lrw " +
					"INNER JOIN logical_webmethods AS lwm ON lrw.log_wmeth_id = lwm.log_wmeth_id " +
					"WHERE lwm.is_deleted = 0 AND (lrw.webmethod_id = @webmethod_id OR lrw.proxy_callback_wmeth_id = @webmethod_id OR " +
					"lrw.callback_webmethod_id = @webmethod_id OR lrw.proxy_webmethod_id = @webmethod_id)";
				obj = execScalar(query2, new SqlParameter("@webmethod_id", (object)webMethodID));
				if ( obj is int ) {
					logwmeth = this.RepositoryService.GetItem<LogicalWebMethod>((int)obj);
				} else if ( obj == null ) {
					const string query3 =
						"SELECT log_wmeth_id FROM logical_webmethods " +
						"WHERE callback_webmethod_id = @webmethod_id AND is_deleted = 0";
					obj = execScalar(query3, new SqlParameter("@webmethod_id", (object)webMethodID));
					if ( obj is int ) {
						logwmeth = this.RepositoryService.GetItem<LogicalWebMethod>((int)obj);
					}
				}
			}
			return logwmeth;
		}
		public BindingListEx<LogicalWebMethod> GetLogicalWebMethodsOfWebMethod(int webMethodID)
		{
			const string query =
				"SELECT * FROM logical_webmethods " +
				"WHERE webmethod_id = @webmethod_id AND is_deleted = 0";

			DataTable dt = fillDataTable(query, new SqlParameter("@webmethod_id", webMethodID));

			BindingListEx<LogicalWebMethod> lwms = fillSSBList<LogicalWebMethod>(dt);

			const string query2 =
				"SELECT log_wmeth_id FROM logmeth_rule_webmethods " +
				"WHERE webmethod_id = @webmethod_id OR proxy_callback_wmeth_id = @webmethod_id OR " +
				"callback_webmethod_id = @webmethod_id OR proxy_webmethod_id = @webmethod_id";

			DataTable dt2 = fillDataTable(query2, new SqlParameter("@webmethod_id", webMethodID));

			lwms.AddRangeDistinct(fillSSBList<LogicalWebMethod>(dt2));
			return lwms;
		}

		public LogicalWebMethod GetLogicalWebMethodOfHumanActivity(int humanActivityID)
		{
			const string query =
				"SELECT log_wmeth_id FROM logical_webmethods WHERE hact_id = @hact_id";

			LogicalWebMethod logwmeth = null;

			object obj = execScalar(query, new SqlParameter("@hact_id", (object)humanActivityID));
			if ( obj is int ) {
				logwmeth = this.RepositoryService.GetItem<LogicalWebMethod>((int)obj);
			}
			return logwmeth;
		}

		public BindingListEx<LogicalWebMethod> GetLogicalWebMethodsOfHumanActivity(int humanActivityID)
		{
			const string query =
				"SELECT * FROM logical_webmethods WHERE hact_id = @hact_id";

			DataTable dt = fillDataTable(query, new SqlParameter("@hact_id", humanActivityID));

			return fillSSBList<LogicalWebMethod>(dt);
		}


		public DataTable GetLogicalWebMethods(
			ItemType parentType, int parentId, int inputBObjId, int outputBObjId)
		{
			const string query =
				"SET ANSI_NULLS OFF; " +
				"SELECT 81 as [type], log_wmeth_id as [id], log_wmeth_nm as [name], log_wmeth_desc as [desc], " +
					"created_dt, last_modified_dt, " +
				"(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.user_id = l.created_by) AS created_nm, " +
				"(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.user_id = l.locked_by) AS locked_nm, " +
				"(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.user_id = l.last_modified_by) AS last_modified_nm " +
				"FROM logical_webmethods l " +
				"WHERE input_bobj_id = @input_bobj_id AND output_bobj_id = @output_bobj_id AND " +
					"is_deleted = 0 AND log_wmeth_id IN ({0}); " +
				"SET ANSI_NULLS ON;";
			const string queryEmpty =
				"SET ANSI_NULLS OFF; " +
				"SELECT 81 as [type], log_wmeth_id as [id], log_wmeth_nm as [name], log_wmeth_desc as [desc], " +
					"created_dt, last_modified_dt, " +
				"(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.user_id = l.created_by) AS created_nm, " +
				"(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.user_id = l.locked_by) AS locked_nm, " +
				"(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.user_id = l.last_modified_by) AS last_modified_nm " +
				"FROM logical_webmethods l " +
				"WHERE 1 = 0; " +
				"SET ANSI_NULLS ON;";

			string logwmethids = "";
			DataTable dt = null;
			if ( parentType == ItemType.Undefined ) {
				dt = Global.Data.GetRepositoryHierarchyChildRepositoryList(parentType, parentId, Global.CurrentUser.OrganizationID, ItemType.LogicalWebMethod);
			} else {
				dt = Global.Data.GetRepositoryHierarchyChildRepositoryList(parentType, parentId);
			}
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					if ( (ItemType)row["type"] == ItemType.LogicalWebMethod ) {
						if ( logwmethids.Length > 0 ) logwmethids += ",";
						logwmethids += row["id"].ToString();
					}
				}
				if ( !string.IsNullOrEmpty(logwmethids) ) {
					return fillDataTable(string.Format(query, logwmethids),
						new SqlParameter("@input_bobj_id", inputBObjId > 0 ? (object)inputBObjId : (object)DBNull.Value),
						new SqlParameter("@output_bobj_id", outputBObjId > 0 ? (object)outputBObjId : (object)DBNull.Value));
				}
			}
			return fillDataTable(queryEmpty);
		}
		public DataTable GetVerbs(
			ItemType parentType, int parentId, int inputBObjId, int outputBObjId)
		{
			const string query =
				"SET ANSI_NULLS OFF; " +
				"SELECT verb_id as [id], verb_nm as [name], verb_desc as [desc], verb_ntext as [ntext], " +
				"verb_input_bobj_id, verb_output_bobj_id, capability_id, log_wmeth_id, diagram, org_id, " +
				"domain_id, is_folder, is_locked, locked_dt, locked_by, created_type, created_dt, " +
				"created_by, last_modified_dt, last_modified_by, is_deleted, deleted_dt, deleted_by, " +
				"14 as [type], " +
				"(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.user_id = v.created_by) AS created_nm, " +
				"(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.user_id = v.locked_by) AS locked_nm, " +
				"(SELECT LTRIM(RTRIM(u.first_nm)) + ' ' + LTRIM(RTRIM(u.last_nm)) FROM users u WHERE u.user_id = v.last_modified_by) AS last_modified_nm " +
				"FROM verbs v " +
				"WHERE verb_input_bobj_id = @input_bobj_id AND verb_output_bobj_id = @output_bobj_id AND " +
				" verb_id IN ({0}) AND is_deleted = 0; " +
				"SET ANSI_NULLS ON;";

			string verbIds = "";
			DataTable dt = null;
			if ( parentType == ItemType.Undefined ) {
				dt = Global.Data.GetRepositoryHierarchyChildRepositoryList(parentType, parentId, Global.CurrentUser.OrganizationID, ItemType.Verb);
			} else {
				dt = Global.Data.GetRepositoryHierarchyChildRepositoryList(parentType, parentId);
			}
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					if ( (ItemType)row["type"] == ItemType.Verb ) {
						if ( verbIds.Length > 0 ) verbIds += ",";
						verbIds += row["id"].ToString();
					}
				}
				if ( !string.IsNullOrEmpty(verbIds) ) {
					return fillDataTable(string.Format(query, verbIds),
						new SqlParameter("@input_bobj_id", inputBObjId > 0 ? (object)inputBObjId : (object)DBNull.Value),
						new SqlParameter("@output_bobj_id", outputBObjId > 0 ? (object)outputBObjId : (object)DBNull.Value));
				}
			}
			return null;
		}

		public void SetDefaultLogWMethAtLogicalActivitiesOfCapability(int capabilityID, int logwmethID)
		{
			const string query =
				"UPDATE logical_activities SET log_wmeth_id = @log_wmeth_id " +
				"WHERE log_act_verb_id = @log_act_verb_id AND " +
				" ( log_wmeth_id IS NULL OR log_wmeth_id <= 0 )";

			execNonQuery(query,
						 new SqlParameter("@log_wmeth_id", (object)logwmethID),
						 new SqlParameter("@log_act_verb_id", (object)capabilityID));
		}

		public BindingListEx<LogicalRuleResultEndpoint> GetLogicalRuleResultEndpoints(int logMethodID)
		{
			// select all items of logical method
			const string querySelect =
				"SELECT * FROM logmeth_rule_webmethods WHERE log_wmeth_id = @log_wmeth_id";
			// create a new list of LogicalRuleResultEndpoints
			BindingListEx<LogicalRuleResultEndpoint> lst = new BindingListEx<LogicalRuleResultEndpoint>();
			// get the items from the database
			DataTable dt = fillDataTable(querySelect, new SqlParameter("@log_wmeth_id", (object)logMethodID));
			if ( dt == null || dt.Rows.Count == 0 ) return lst;
			// create objects, set values and add object to item list
			foreach ( DataRow row in dt.Rows ) {
				LogicalRuleResultEndpoint lrre = new LogicalRuleResultEndpoint(_serviceProvider, logMethodID);
				lrre.EndpointRuleResult = getString(row, "endpoint_brule_result", null);
				lrre.WebMethodID = getInt(row, "webmethod_id", -1);
				lrre.CallbackWebMethodID = getInt(row, "callback_webmethod_id", -1);
				lrre.ProxyWebMethodID = getInt(row, "proxy_webmethod_id", -1);
				lrre.ProxyCallbackWebMethodID = getInt(row, "proxy_callback_wmeth_id", -1);
				lrre.HumanActivityID = getInt(row, "hact_id", -1);
				lrre.InputXSLT = getXsltDocument(row, "input_xslt");
				lrre.InputConnections = getString(row, "input_connections", null);
				lrre.OutputXSLT = getXsltDocument(row, "output_xslt");
				lrre.OutputConnections = getString(row, "output_connections", null);
				lrre.ProxyInputXSLT = getXsltDocument(row, "proxy_input_xslt");
				lrre.ProxyInputConnections = getString(row, "proxy_input_connections", null);
				lrre.ProxyOutputXSLT = getXsltDocument(row, "proxy_output_xslt");
				lrre.ProxyOutputConnections = getString(row, "proxy_output_connections", null);
				lst.Add(lrre);
			}
			return lst;
		}
		public void SetLogicalRuleResultEndpoints(int logMethodID, List<LogicalRuleResultEndpoint> items)
		{
			// delete all items of logical method
			const string queryDelete =
				"DELETE FROM logmeth_rule_webmethods WHERE log_wmeth_id = @log_wmeth_id";
			// select all items of logical method
			const string querySelect =
				"SELECT * FROM logmeth_rule_webmethods WHERE log_wmeth_id = @log_wmeth_id";

			DataTable dt = null;
			lock ( this.lockThis ) {
				SqlTransaction trans = beginTransaction();
				try {
					// delete existing rows for the current logical method
					execNonQuery(trans, queryDelete, new SqlParameter("@log_wmeth_id", (object)logMethodID));
					if ( items != null && items.Count > 0 ) {
						// get the data table
						dt = fillDataTable(trans, querySelect, new SqlParameter("@log_wmeth_id", (object)logMethodID));
						// copy items to data table
						dt.BeginLoadData();
						foreach ( var item in items ) {
							dt.LoadDataRow(new object[] {
								item.LogicalMethodID,
								item.EndpointRuleResult,
								item.WebMethodID > 0 ? (object)item.WebMethodID : (object)null,
								item.CallbackWebMethodID > 0 ? (object)item.CallbackWebMethodID : (object)null,
								item.ProxyWebMethodID > 0 ? (object)item.ProxyWebMethodID : (object)null,
								item.ProxyCallbackWebMethodID > 0 ? (object)item.ProxyCallbackWebMethodID : (object)null,
								item.HumanActivityID > 0 ? (object)item.HumanActivityID : (object)null,
								item.InputXSLT == null ? null : item.InputXSLT.InnerXml,
								item.InputConnections,
								item.OutputXSLT == null ? null : item.OutputXSLT.InnerXml,
								item.OutputConnections,
								item.ProxyInputXSLT == null ? null : item.ProxyInputXSLT.InnerXml,
								item.ProxyInputConnections,
								item.ProxyOutputXSLT == null ? null : item.ProxyOutputXSLT.InnerXml,
								item.ProxyOutputConnections
							}, true);
						}
						dt.EndLoadData();
						// bulk copy the items to the database
						bulkCopy(trans, "logmeth_rule_webmethods", dt);
					}
					commitTransaction(trans);
				} catch ( Exception ex ) {
					System.Diagnostics.Debug.WriteLine(ex.ToString());
					rollbackTransaction(trans);
				}
			}
		}

		public BindingListEx<LogicalWebMethod> GetLogicalMethodsOfVerb(int verbID)
		{
			const string query =
				"SELECT lm.* FROM logical_webmethods lm " +
				"WHERE lm.log_wmeth_id IN ( " +
				"SELECT la.log_wmeth_id FROM logical_activities la " +
				"WHERE la.log_act_verb_id = @log_act_verb_id ) AND lm.is_deleted = 0";
			BindingListEx<LogicalWebMethod> lst = new BindingListEx<LogicalWebMethod>();

			DataTable dt = fillDataTable(query, new SqlParameter("@log_act_verb_id", (object)verbID));

			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					lst.Add(this.RepositoryService.GetItem(ItemType.LogicalWebMethod, row) as LogicalWebMethod);
				}
			}
			return lst;
		}
		public BindingListEx<Verb> GetVerbsOfLogicalMethod(int logMethID)
		{
			const string query =
				"SELECT v.* FROM verbs v " +
				"WHERE v.verb_id IN ( " +
				"SELECT la.log_act_verb_id FROM logical_activities la " +
				"WHERE la.log_wmeth_id = @log_wmeth_id AND la.is_deleted = 0) AND v.is_deleted = 0";
			const string queryDefault =
				"SELECT v.* FROM verbs v " +
				"WHERE v.log_wmeth_id = @log_wmeth_id AND v.is_deleted = 0";
			BindingListEx<Verb> lst = new BindingListEx<Verb>();

			DataTable dt = fillDataTable(query, new SqlParameter("@log_wmeth_id", (object)logMethID));
			DataTable dt2 = fillDataTable(queryDefault, new SqlParameter("@log_wmeth_id", (object)logMethID));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					lst.Add(this.RepositoryService.GetItem(ItemType.Verb, row) as Verb);
				}
			}
			if ( dt2 != null && dt2.Rows.Count > 0 ) {
				foreach ( DataRow row in dt2.Rows ) {
					Verb v = this.RepositoryService.GetItem(ItemType.Verb, row) as Verb;
					if ( !lst.Contains(v) ) lst.Add(v);
				}
			}
			return lst;
		}
		public BindingListEx<Verb> GetVerbsOfWebMethod(int webMethID)
		{
			const string query =
				"SELECT v.* FROM verbs v, logical_webmethods lwm, webmethods wm " +
				"WHERE wm.webmethod_id = @webmethod_id " +
				"AND v.log_wmeth_id = lwm.log_wmeth_id AND lwm.webmethod_id = wm.webmethod_id " +
				"AND v.is_deleted = 0 AND lwm.is_deleted = 0 AND wm.is_deleted = 0";

			BindingListEx<Verb> lst = new BindingListEx<Verb>();

			DataTable dt = fillDataTable(query, new SqlParameter("@webmethod_id", (int)webMethID));
			lst = fillSSBList<Verb>(dt);
			return lst;
		}

		public BindingListEx<WebMethod> GetWebMethodsOfWebServices(int webSvcID)
		{
			string query = "SELECT wm.* FROM webmethods wm, webservices ws " +
					"WHERE ws.websvc_id = @websvc_id AND wm.websvc_id = ws.websvc_id " +
					"AND ws.is_deleted = 0 AND wm.is_deleted = 0";
			BindingListEx<WebMethod> lstVerbs = new BindingListEx<WebMethod>();
			DataTable dt = fillDataTable(query, new SqlParameter("@websvc_id", webSvcID));

			lstVerbs = fillSSBList<WebMethod>(dt);

			return lstVerbs;

		}

		public BindingListEx<LogicalWebMethod> GetLogicalMethodsOfNoun(int nounID)
		{
			string query = "DECLARE @countold int" + Environment.NewLine +
				"DECLARE @countnew int" + Environment.NewLine +

				"CREATE TABLE #bos ( bobj_id int )" + Environment.NewLine +

				"INSERT INTO #bos" + Environment.NewLine +
				"SELECT bobj_id FROM bobj_items WHERE item_noun_id = @noun_id" + Environment.NewLine +

				"SET @countold = -1" + Environment.NewLine +
				"SET @countnew = 0" + Environment.NewLine +

				"WHILE @countold != @countnew" + Environment.NewLine +
				"BEGIN" + Environment.NewLine +

					"SELECT @countold = count(*) FROM #bos" + Environment.NewLine +

					"INSERT INTO #bos" + Environment.NewLine +
					"SELECT	bobj_id FROM bobj_items" + Environment.NewLine +
					"WHERE	item_bobj_id IN ( SELECT bobj_id FROM #bos ) AND" + Environment.NewLine +
					"		bobj_id NOT IN ( SELECT bobj_id FROM #bos )" + Environment.NewLine +

					"SELECT @countnew = count(*) FROM #bos" + Environment.NewLine +
				"END" + Environment.NewLine +

				"SELECT DISTINCT logical_webmethods.log_wmeth_id FROM logical_webmethods " +
				"WHERE ( input_bobj_id IN ( SELECT bobj_id FROM #bos ) OR output_bobj_id IN ( SELECT bobj_id FROM #bos ) ) " +
				"AND logical_webmethods.is_deleted = 0" +

				"DROP TABLE #bos";


			//string query = "SELECT logical_webmethods.* FROM logical_webmethods " +
			//    "INNER JOIN bobj_items ON (logical_webmethods.input_bobj_id = bobj_items.bobj_id OR logical_webmethods.input_bobj_id = bobj_items.item_bobj_id) " +
			//        "OR (logical_webmethods.output_bobj_id = bobj_items.bobj_id OR logical_webmethods.output_bobj_id = bobj_items.item_bobj_id) " +
			//    "INNER JOIN nouns ON bobj_items.item_noun_id = nouns.noun_id " +
			//    "WHERE (nouns.noun_id = @noun_id)";

			DataTable dt = fillDataTable(query, new SqlParameter("@noun_id", nounID));

			return fillSSBList<LogicalWebMethod>(dt);
		}

		#endregion

		#region Business Objects
		private const string SQL_GetBusinessObjectItems =
			"SELECT * FROM bobj_items WHERE bobj_id = @bobj_id";
		private const string SQL_DeleteBusinessObjectItems =
			"DELETE FROM bobj_items WHERE bobj_id = @bobj_id";
		private const string SQL_InsertBusinessObjectItems =
			"INSERT INTO bobj_items (bobj_id, item_bobj_id, item_noun_id, item_nm, " +
				"item_minoccurs, item_maxoccurs, item_is_attribute) " +
			"VALUES (@bobj_id, @item_bobj_id, @item_noun_id, @item_nm, " +
				"@item_minoccurs, @item_maxoccurs, @item_is_attribute)";
		private const string SQL_GetParentBusinessObjects =
			"SELECT bo.* FROM business_objects bo " +
			"INNER JOIN bobj_items bi ON bi.bobj_id = bo.bobj_id " +
			"WHERE bi.item_bobj_id = @item_bobj_id AND bo.is_deleted = 0";
		private const string SQL_GetVerbsOfBusinesssObject =
			"SELECT v.* FROM business_objects bo, verbs v " +
			"WHERE bo.bobj_id = @bobj_id AND (bo.bobj_id = v.verb_input_bobj_id OR bo.bobj_id = v.verb_output_bobj_id) " +
			"AND bo.is_deleted = 0 AND v.is_deleted = 0";
		private const string SQL_GetWorkflowsOfBusinesssObject =
			"SELECT wf.* FROM business_objects bo, workflows wf " +
			"WHERE bo.bobj_id = @bobj_id AND (bo.bobj_id = wf.workflow_input_bobj_id OR bo.bobj_id = wf.workflow_output_bobj_id) " +
			"AND wf.is_deleted = 0 AND bo.is_deleted = 0";

		public DataTable GetBusinessObjectItems(int bobjectID)
		{
			return fillDataTable(SQL_GetBusinessObjectItems, new SqlParameter("@bobj_id", bobjectID));
		}

		public void SetBusinessObjectItems(int bobjectID, DataTable dtItems)
		{
			lock ( lockThis ) {
				SqlTransaction trans = beginTransaction();
				try {
					execNonQuery(trans, SQL_DeleteBusinessObjectItems, new SqlParameter("@bobj_id", bobjectID));

					bulkCopy(trans, "bobj_items", dtItems);

					commitTransaction(trans);
				} catch {
					rollbackTransaction(trans);
				}
			}
		}
		public void DeleteBusinessObjectItems(int bobjectID)
		{
			execNonQuery(SQL_DeleteBusinessObjectItems, new SqlParameter("@bobj_id", bobjectID));
		}
		public BindingListEx<BusinessObject> GetParentBusinessObjects(int bobjectID)
		{
			BindingListEx<BusinessObject> lstBOs = new BindingListEx<BusinessObject>();
			DataTable dt = fillDataTable(SQL_GetParentBusinessObjects, new SqlParameter("@item_bobj_id", bobjectID));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					BusinessObject bo = RepositoryService.GetItem(ItemType.BObject, row) as BusinessObject;
					if ( bo != null )
						lstBOs.Add(bo);
				}
			}
			return lstBOs;
		}

		public BindingListEx<BusinessObject> GetBusinessObjectsOfNoun(int nounID)
		{
			const string query =
				"SELECT bi.bobj_id FROM bobj_items bi, business_objects bo WHERE bi.item_noun_id = @item_noun_id " +
				"AND bi.bobj_id = bo.bobj_id AND bo.is_deleted = 0";

			BindingListEx<BusinessObject> lstBOs = new BindingListEx<BusinessObject>();
			DataTable dt = fillDataTable(query, new SqlParameter("@item_noun_id", (object)nounID));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					BusinessObject bo = RepositoryService.GetItem<BusinessObject>((int)row["bobj_id"]);
					if ( bo != null ) {
						lstBOs.Add(bo);
					}
				}
			}
			return lstBOs;
		}
		public BindingListEx<Verb> GetVerbsOfBusinessObject(int bobjectID)
		{
			BindingListEx<Verb> lstVerbs = new BindingListEx<Verb>();
			DataTable dt = fillDataTable(SQL_GetVerbsOfBusinesssObject, new SqlParameter("@bobj_id", bobjectID));

			lstVerbs = fillSSBList<Verb>(dt);

			return lstVerbs;
		}

		public BindingListEx<Repository.Workflow> GetWorkflowsOfBusinessObject(int bobjectID)
		{
			BindingListEx<Repository.Workflow> lstVerbs = new BindingListEx<Repository.Workflow>();
			DataTable dt = fillDataTable(SQL_GetWorkflowsOfBusinesssObject, new SqlParameter("@bobj_id", bobjectID));

			lstVerbs = fillSSBList<Repository.Workflow>(dt);

			return lstVerbs;
		}

		public BindingListEx<Repository.Workflow> GetWorkflowsOfVerb(int verbID)
		{
			const string SQL_GetWorkflowsOfVerb =
			"SELECT wf.* FROM verbs v, logical_activities la, workflows wf " +
			"WHERE v.verb_id = @verb_id AND v.verb_id = la.log_act_verb_id AND la.workflow_id = wf.workflow_id AND " +
			"wf.is_deleted = 0 AND v.is_deleted = 0 AND la.is_deleted = 0";

			BindingListEx<Repository.Workflow> lstVerbs = new BindingListEx<Repository.Workflow>();
			DataTable dt = fillDataTable(SQL_GetWorkflowsOfVerb, new SqlParameter("@verb_id", verbID));

			lstVerbs = fillSSBList<Repository.Workflow>(dt);

			return lstVerbs;
		}

		public BindingListEx<WorkflowField> GetWorkflowFieldOfBusinessObject(int bobjectID)
		{
			const string query = "SELECT wf_fields.* FROM wf_fields, business_objects bo, workflows " +
							"WHERE wf_fields.wf_field_bobj_id = bo.bobj_id AND bo.bobj_id = @bobj_id " +
							"AND bo.is_deleted = 0 AND wf_fields.workflow_id = workflows.workflow_id AND workflows.is_deleted = 0";
			BindingListEx<WorkflowField> lstVerbs = new BindingListEx<WorkflowField>();
			DataTable dt = fillDataTable(query, new SqlParameter("@bobj_id", bobjectID));

			lstVerbs = fillSSBList<WorkflowField>(dt);

			return lstVerbs;
		}

		public BindingListEx<WorkflowMethod> GetWorkflowMethodOfBusinessObject(int bobjectID)
		{
			const string query = "SELECT wm.* FROM wf_methods wm, business_objects bo, workflows " +
							"WHERE bo.bobj_id = @bobj_id AND " +
							"(wm.wf_method_input_bobj_id = bo.bobj_id OR wm.wf_method_output_bobj_id = bo.bobj_id) " +
							"AND bo.is_deleted = 0 AND wm.workflow_id = workflows.workflow_id AND workflows.is_deleted = 0";
			BindingListEx<WorkflowMethod> lstVerbs = new BindingListEx<WorkflowMethod>();
			DataTable dt = fillDataTable(query, new SqlParameter("@bobj_id", bobjectID));

			lstVerbs = fillSSBList<WorkflowMethod>(dt);

			return lstVerbs;
		}

		public BindingListEx<WorkflowEvent> GetWorkflowEventOfBusinessObject(int bobjectID)
		{
			const string query = "SELECT wf_events.* FROM wf_events, business_objects bo, workflows " +
							"WHERE wf_events.wf_event_bobj_id = bo.bobj_id AND bo.bobj_id = @bobj_id " +
							"AND bo.is_deleted = 0 AND wf_events.workflow_id = workflows.workflow_id AND workflows.is_deleted = 0";
			BindingListEx<WorkflowEvent> lstVerbs = new BindingListEx<WorkflowEvent>();
			DataTable dt = fillDataTable(query, new SqlParameter("@bobj_id", bobjectID));

			lstVerbs = fillSSBList<WorkflowEvent>(dt);

			return lstVerbs;
		}

		public BindingListEx<LogicalWebMethod> GetLogicalMethodsOfBusinessObject(int bobjectID)
		{
			const string query = "SELECT lm.* FROM logical_webmethods lm, business_objects bo " +
							"WHERE bo.bobj_id = @bobj_id AND " +
							"(lm.input_bobj_id = bo.bobj_id OR lm.output_bobj_id = bo.bobj_id) " +
							"AND bo.is_deleted = 0 AND lm.is_deleted = 0";
			BindingListEx<LogicalWebMethod> lstVerbs = new BindingListEx<LogicalWebMethod>();
			DataTable dt = fillDataTable(query, new SqlParameter("@bobj_id", bobjectID));

			lstVerbs = fillSSBList<LogicalWebMethod>(dt);

			return lstVerbs;
		}

		public BusinessObject GetContainerBusinessObjectOfWorkflowField(int wfFieldID)
		{
			const string query =
				"SELECT * FROM business_objects WHERE wf_field_id = @wf_field_id";

			DataTable dt = fillDataTable(query,
				new SqlParameter("@wf_field_id", (object)wfFieldID));

			BusinessObject bo = null;
			if ( dt != null && dt.Rows.Count > 0 ) {
				bo = this.RepositoryService.GetItem(ItemType.BObject, dt.Rows[0]) as BusinessObject;
			}
			return bo;
		}
		#endregion

		#region Business Rules

		#region Querries
		private const string SQL_GetRuleConstantIDsOfBusinessRule =
			"SELECT brrc.ruleconst_id FROM brule_ruleconstants brrc " +
			"INNER JOIN rule_constants rc ON rc.ruleconst_id = brrc.ruleconst_id " +
			"WHERE brrc.brule_id = @brule_id AND rc.is_deleted = 0";
		private const string SQL_DeleteRuleConstantIDsOfBusinessRule =
			"DELETE FROM brule_ruleconstants WHERE brule_id = @brule_id";
		private const string SQL_AddRuleConstantIDofBusinessRule =
			"IF NOT EXISTS (SELECT * FROM brule_ruleconstants " +
							"WHERE brule_id = @brule_id AND ruleconst_id = @ruleconst_id) " +
			"BEGIN " +
				"INSERT INTO brule_ruleconstants (brule_id, ruleconst_id) " +
				"VALUES (@brule_id, @ruleconst_id) " +
			"END";
		#endregion

		public List<int> GetRuleConstantIDs(int bruleID)
		{
			List<int> ruleConstIDs = new List<int>();
			DataTable dt = fillDataTable(SQL_GetRuleConstantIDsOfBusinessRule,
				new SqlParameter("@brule_id", bruleID));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					int id = (int)row["ruleconst_id"];
					if ( !ruleConstIDs.Contains(id) )
						ruleConstIDs.Add(id);
				}
			}
			return ruleConstIDs;
		}
		public void SetRuleConstantIDs(int bruleID, List<int> ruleConstIDs)
		{
			lock ( lockThis ) {
				SqlTransaction trans = this.beginTransaction();
				try {
					execNonQuery(trans, SQL_DeleteRuleConstantIDsOfBusinessRule,
						new SqlParameter("@brule_id", bruleID));

					foreach ( int ruleconstid in ruleConstIDs ) {
						execNonQuery(trans, SQL_AddRuleConstantIDofBusinessRule,
							new SqlParameter("@brule_id", bruleID),
							new SqlParameter("@ruleconst_id", ruleconstid));
					}
					this.commitTransaction(trans);
				} catch ( Exception ex ) {
					this.rollbackTransaction(trans);
					throw ex;
				}
			}
		}
		public List<int> GetNounIDsOfBusinessRule(int bruleID)
		{
			string query = "SELECT noun_id FROM brule_nouns " +
				"WHERE brule_id = @brule_id";

			List<int> lstNouns = new List<int>();
			DataTable dt = fillDataTable(query, new SqlParameter("@brule_id", bruleID));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					int id = (int)row["noun_id"];
					if ( !lstNouns.Contains(id) )
						lstNouns.Add(id);
				}
			}

			return lstNouns;
		}
		public void SetNounIDsOfBusinessRule(int bruleID, List<int> usedNouns)
		{
			string deleteQuery =
			   "DELETE FROM brule_nouns WHERE brule_id = @brule_id";
			string insertQuery =
				"IF NOT EXISTS (SELECT * FROM brule_nouns " +
								"WHERE brule_id = @brule_id AND noun_id = @noun_id) " +
				"BEGIN " +
					"INSERT INTO brule_nouns (brule_id, noun_id) " +
					"VALUES (@brule_id, @noun_id) " +
				"END";
			lock ( lockThis ) {
				SqlTransaction trans = this.beginTransaction();
				try {
					execNonQuery(trans, deleteQuery,
						new SqlParameter("@brule_id", bruleID));

					foreach ( int nounid in usedNouns ) {
						execNonQuery(trans, insertQuery,
							new SqlParameter("@brule_id", bruleID),
							new SqlParameter("@noun_id", nounid));
					}
					this.commitTransaction(trans);
				} catch ( Exception ex ) {
					this.rollbackTransaction(trans);
					throw ex;
				}
			}
		}
		public List<int> GetBusinessRuleIDsOfWorkflow(int workflowID)
		{
			string query = "SELECT brule_id FROM workflow_brules " +
				"WHERE brule_id = @brule_id";

			List<int> lstNouns = new List<int>();
			DataTable dt = fillDataTable(query, new SqlParameter("@brule_id", workflowID));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					int id = (int)row["noun_id"];
					if ( !lstNouns.Contains(id) )
						lstNouns.Add(id);
				}
			}

			return lstNouns;
		}
		public void SetBusinessRuleIDsOfWorkflow(int workflowId, List<int> usedBRuleIDs)
		{
			string deleteQuery =
			   "DELETE FROM workflow_brules WHERE workflow_id = @workflow_id";
			string insertQuery =
				"IF NOT EXISTS (SELECT * FROM workflow_brules " +
								"WHERE brule_id = @brule_id AND workflow_id = @workflow_id) " +
				"BEGIN " +
					"INSERT INTO workflow_brules (brule_id, workflow_id) " +
					"VALUES (@brule_id, @workflow_id) " +
				"END";
			lock ( lockThis ) {
				SqlTransaction trans = this.beginTransaction();
				try {
					execNonQuery(trans, deleteQuery,
						new SqlParameter("@workflow_id", workflowId));

					foreach ( int bruleID in usedBRuleIDs ) {
						execNonQuery(trans, insertQuery,
							new SqlParameter("@brule_id", bruleID),
							new SqlParameter("@workflow_id", workflowId));
					}
					this.commitTransaction(trans);
				} catch ( Exception ex ) {
					this.rollbackTransaction(trans);
					throw ex;
				}
			}
		}


		public BindingListEx<RepositoryBusinessRule> GetBusinessRulesOfNoun(int nounID)
		{
			string query = "SELECT r.* FROM nouns n, brule_nouns bn, repository_business_rules r " +
				"WHERE n.noun_id = bn.noun_id AND bn.brule_id = r.brule_id " +
				"AND n.noun_id = @noun_id AND r.is_deleted = 0 AND n.is_deleted = 0";
			BindingListEx<RepositoryBusinessRule> lstRules = new BindingListEx<RepositoryBusinessRule>();
			DataTable dt = fillDataTable(query, new SqlParameter("@noun_id", nounID));

			lstRules = fillSSBList<RepositoryBusinessRule>(dt);

			return lstRules;
		}
		public BindingListEx<RepositoryBusinessRule> GetBusinessRulesOfRuleConstant(int constID)
		{
			string query = "SELECT r.* FROM rule_constants c, brule_ruleconstants rc, repository_business_rules r " +
				"WHERE c.ruleconst_id = rc.ruleconst_id AND rc.brule_id = r.brule_id " +
				"AND c.ruleconst_id = @ruleconst_id AND r.is_deleted = 0 AND c.is_deleted = 0";
			BindingListEx<RepositoryBusinessRule> lstRules = new BindingListEx<RepositoryBusinessRule>();
			DataTable dt = fillDataTable(query, new SqlParameter("@ruleconst_id", constID));

			lstRules = fillSSBList<RepositoryBusinessRule>(dt);

			return lstRules;

		}

		#endregion

		#region Workflow

		public BindingListEx<WorkflowMethod> GetWorkflowMethods(int idWorkflow)
		{
			const string query = "SELECT * FROM wf_methods WHERE workflow_id = @workflow_id";

			BindingListEx<WorkflowMethod> lst = new BindingListEx<WorkflowMethod>();
			DataTable dt = fillDataTable(query, new SqlParameter("@workflow_id", (object)idWorkflow));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					WorkflowMethod item = this.RepositoryService.GetItem(ItemType.Workflow_Method, row)
						as WorkflowMethod;
					if ( item != null )
						lst.Add(item);
				}
			}
			return lst;
		}
		public void SetWorkflowMethods(int idWorkflow, IEnumerable<WorkflowMethod> methods)
		{
			foreach ( WorkflowMethod item in methods ) {
				item.WorkflowID = idWorkflow;
				item.Save();
			}
		}

		public BindingListEx<WorkflowEvent> GetWorkflowEvents(int idWorkflow)
		{
			const string query = "SELECT * FROM wf_events WHERE workflow_id = @workflow_id";
			BindingListEx<WorkflowEvent> lst = new BindingListEx<WorkflowEvent>();
			DataTable dt = fillDataTable(query, new SqlParameter("@workflow_id", (object)idWorkflow));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					WorkflowEvent item = this.RepositoryService.GetItem(ItemType.Workflow_Event, row)
						as WorkflowEvent;
					if ( item != null )
						lst.Add(item);
				}
			}
			return lst;
		}
		public void SetWorkflowEvents(int idWorkflow, IEnumerable<WorkflowEvent> events)
		{
			foreach ( WorkflowEvent item in events ) {
				item.WorkflowID = idWorkflow;
				item.Save();
			}
		}

		public BindingListEx<WorkflowField> GetWorkflowFields(int idWorkflow)
		{
			const string query = "SELECT * FROM wf_fields WHERE workflow_id = @workflow_id";
			BindingListEx<WorkflowField> lst = new BindingListEx<WorkflowField>();
			DataTable dt = fillDataTable(query, new SqlParameter("@workflow_id", (object)idWorkflow));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					WorkflowField item = this.RepositoryService.GetItem(ItemType.Workflow_Field, row)
						as WorkflowField;
					if ( item != null )
						lst.Add(item);
				}
			}
			return lst;
		}
		public void SetWorkflowFields(int idWorkflow, IEnumerable<WorkflowField> fields)
		{
			foreach ( WorkflowField item in fields ) {
				item.WorkflowID = idWorkflow;
				item.Save();
			}
		}

		public BindingListEx<Repository.Workflow> GetWorkflowsFromBusinessRule(int bRuleID)
		{
			string query = "SELECT w.* FROM repository_business_rules r, workflow_brules wb, workflows w " +
				"WHERE w.workflow_id = wb.workflow_id AND wb.brule_id = r.brule_id " +
				"AND r.brule_id = @brule_id AND r.is_deleted = 0 AND w.is_deleted = 0";
			BindingListEx<Repository.Workflow> lstWorkflows = new BindingListEx<Repository.Workflow>();
			DataTable dt = fillDataTable(query, new SqlParameter("@brule_id", bRuleID));

			lstWorkflows = fillSSBList<Repository.Workflow>(dt);

			return lstWorkflows;

		}
		public BindingListEx<Repository.Workflow> GetWorkflowsFromWorkflow(int workflowID)
		{
			//string query = "";
			BindingListEx<Repository.Workflow> workflows = new BindingListEx<OpenComposite.EII.Repository.Workflow>();

			return workflows;
		}
		//public SearchableSortableBindingList<Repository.Workflow> GetWorkflowsFromWebSerivce(int webSvcID)
		//{
		//    string query = "SELECT w.* FROM workflows w,
		//}

		public BindingListEx<LogicalActivity> GetLogicalActivitiesOfWorkflow(int workflowID)
		{
			string query = "SELECT * FROM logical_activities WHERE workflow_id = @workflow_id AND is_deleted = 0";

			DataTable dt = fillDataTable(query, new SqlParameter("@workflow_id", workflowID));

			return fillSSBList<LogicalActivity>(dt);
		}

		public List<string> GetWorkflowLocations()
		{
			string query = "SELECT workflow_path FROM workflows";

			List<string> value = new List<string>();
			DataTable dt = fillDataTable(query);
			foreach ( DataRow row in dt.Rows ) {
				if ( row[0] == DBNull.Value ) continue;
				value.Add(row[0].ToString());
			}
			return value;
		}

		#endregion

		#region Capabilities / Capability Methods(Verbs)
		internal BindingListEx<Verb> GetCapabilityMethods(int capabilityID)
		{
			const string query =
				"SELECT * FROM verbs WHERE capability_id = @capability_id";
			DataTable dt = fillDataTable(query,
				new SqlParameter("@capability_id", (object)capabilityID));

			BindingListEx<Verb> capmethods = null;
			if ( dt != null && dt.Rows.Count > 0 ) {
				capmethods = new BindingListEx<Verb>();
				foreach ( DataRow row in dt.Rows ) {
					Verb v = this.RepositoryService.GetItem(ItemType.Verb, row) as Verb;
					if ( v != null ) capmethods.Add(v);
				}
			}
			return capmethods;
		}
		#endregion

		#region Org Designer

		#region queries
		private const string SQL_GetOrgRolesByOrgUnit =
			"SELECT * FROM role " +
			"WHERE is_deleted = 0 AND ((role_isunitspecific = 0 AND role_org_id = @role_org_id)" +
			"OR (role_isunitspecific = 1 AND role_orgunit_id = @role_orgunit_id))";

		private const string SQL_GetOrgUsersByOrgUnit =
			"SELECT * FROM users " +
			"WHERE is_deleted = 0 AND person_orgunit = @person_orgunit";

		private const string SQL_GetOrgUsersByOrg =
			"SELECT * FROM users " +
			"WHERE is_deleted = 0 AND org_id = @person_org";

		private const string SQL_GetOrgOrganisationById =
			"SELECT * FROM organization " +
			"WHERE is_deleted = 0 AND org_id = @organisation_id";

		private const string SQL_GetOrgRolesByOrgUser =
			"SELECT person_roles FROM users " +
			"WHERE is_deleted = 0 AND user_id = @person_id";

		private const string SQL_GetOrgUnitsByOrg =
			"SELECT * FROM organization_unit " +
			"WHERE is_deleted = 0 AND org_id = @orgunit_org_id AND " +
			"(pred_org_unit_id IS NULL OR pred_org_unit_id < 1)";

		private const string SQL_GetOrgUnitsByOrgUnit =
			"SELECT * FROM organization_unit " +
			"WHERE is_deleted = 0 AND pred_org_unit_id = @orgunit_orgunit_id";
		#endregion

		#region methods
		public BindingListEx<Role> GetOrgRolesByOrgUnit(int idOrgUnit)
		{
			string queryOrg = "SELECT * FROM role WHERE is_deleted = 0 AND " +
				"role_orgunit_id = @role_orgunit_id";
			DataTable dt = fillDataTable(
				queryOrg,
				new SqlParameter("@role_orgunit_id", idOrgUnit));
			return fillSSBList<Role>(dt);
		}

		public BindingListEx<Role> GetOrgRolesByOrg(int idOrg)
		{
			string queryOrgUnit = "SELECT * FROM role WHERE is_deleted = 0 AND " +
				"org_id = @role_org_id";
			DataTable dt = fillDataTable(
				queryOrgUnit,
				new SqlParameter("@role_org_id", idOrg));
			return fillSSBList<Role>(dt);
		}

		public BindingListEx<User> GetOrgUsersByOrg(int idOrg)
		{
			BindingListEx<User> lstPersons = new BindingListEx<User>();
			DataTable dt = fillDataTable(
				SQL_GetOrgUsersByOrg,
				new SqlParameter("@person_org", idOrg));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					User user = this.RepositoryService.GetItem(ItemType.User, row) as User;
					if ( user != null )
						lstPersons.Add(user);
				}
			}
			return lstPersons;
		}

		public BindingListEx<User> GetOrgUsersByOrgUnit(int idOrgUnit)
		{
			BindingListEx<User> lstPersons = new BindingListEx<User>();

			DataTable dt = fillDataTable(
				SQL_GetOrgUsersByOrgUnit,
				new SqlParameter("@person_orgunit", idOrgUnit));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					User user = this.RepositoryService.GetItem(ItemType.User, row) as User;
					if ( user != null )
						lstPersons.Add(user);
				}
			}
			return lstPersons;
		}

		public BindingListEx<User> GetOrgUsersByRole(int idRole)
		{
			const string query =
				"SELECT u.* FROM users u " +
				"JOIN user_role_assoc ur ON ur.user_id = u.user_id " +
				"WHERE ur.role_id = @roleid";

			BindingListEx<User> lstUsers = new BindingListEx<User>();
			DataTable dt = fillDataTable(query, new SqlParameter("@roleid", (object)idRole));
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					User user = this.RepositoryService.GetItem(ItemType.User, row) as User;
					if ( user != null ) lstUsers.Add(user);
				}
			}
			return lstUsers;

		}

		public bool ExistsOrgUser(string username)
		{
			const string query = "SELECT count(*) FROM users WHERE [user_nm] = @username";
			int count = (int)execScalar(query, new SqlParameter("@username", username));
			return ( count > 0 );
		}

		public Organization GetOrganisationById(int idOrg)
		{
			Organization org = null;
			DataTable dt = fillDataTable(
				SQL_GetOrgOrganisationById,
				new SqlParameter("@organisation_id", idOrg));

			if ( dt != null && dt.Rows.Count > 0 )
				org = this.RepositoryService.GetItem(ItemType.Organization, dt.Rows[0]) as Organization;

			return org;
		}

		public BindingListEx<Role> GetOrgRolesByOrgUser(int idUser)
		{
			//BindingListEx<RoleEx> lstRoles = new BindingListEx<RoleEx>();
			string query = "SELECT role_id FROM user_role_assoc WHERE user_id = @user_id";
			//OrgOrganisation org;
			DataTable dt = fillDataTable(query, new SqlParameter("@user_id", idUser));
			return fillSSBList<Role>(dt);

			//if ( obj != null ) {
			//    string roles = obj.ToString();
			//    if ( roles != null && roles.Length > 0 ) {
			//        foreach ( string roleId in roles.Split(new char[] { ';' }) ) {
			//            RoleEx role = this.RepositoryService.GetItem(
			//                ItemType.Role, Convert.ToInt32(roleId), false) as RoleEx;
			//            if ( role != null )
			//                lstRoles.Add(role);
			//        }
			//    }
			//}
		}

		public void UpdateOrgUserRoles(int userID, Role[] roles)
		{
			string deleteQuery = "DELETE FROM user_role_assoc WHERE user_id = @user_id";
			string insertQuery = "INSERT INTO user_role_assoc (role_id, user_id) VALUES (@roleID,@user_id)";
			lock ( lockThis ) {
				SqlTransaction trans = this.beginTransaction();
				try {
					execNonQuery(trans, deleteQuery,
						new SqlParameter("@user_id", userID));

					foreach ( Role role in roles ) {
						execNonQuery(trans, insertQuery,
							new SqlParameter("@roleID", role.ID),
							new SqlParameter("@user_id", userID));
					}
					this.commitTransaction(trans);
				} catch ( Exception ex ) {
					this.rollbackTransaction(trans);
					throw ex;
				}
			}
		}

		public BindingListEx<OrganizationalUnit> GetOrgUnitsByOrg(int idOrg)
		{
			BindingListEx<OrganizationalUnit> lstOrgUnits = new BindingListEx<OrganizationalUnit>();

			DataTable dt = fillDataTable(
				SQL_GetOrgUnitsByOrg,
				new SqlParameter("@orgunit_org_id", idOrg));

			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					OrganizationalUnit orgUnit = this.RepositoryService.GetItem(ItemType.OrgUnit, row) as OrganizationalUnit;
					if ( orgUnit != null )
						lstOrgUnits.Add(orgUnit);
				}
			}

			return lstOrgUnits;
		}

		public BindingListEx<OrganizationalUnit> GetOrgUnitsByOrgUnit(int idOrgUnit)
		{
			BindingListEx<OrganizationalUnit> lstOrgUnits = new BindingListEx<OrganizationalUnit>();

			DataTable dt = fillDataTable(
				SQL_GetOrgUnitsByOrgUnit,
				new SqlParameter("@orgunit_orgunit_id", idOrgUnit));

			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					OrganizationalUnit orgUnit = this.RepositoryService.GetItem(ItemType.OrgUnit, row) as OrganizationalUnit;
					if ( orgUnit != null )
						lstOrgUnits.Add(orgUnit);
				}
			}

			return lstOrgUnits;
		}

		public void SetUserOrgUnit(int userId, int orgUnitId)
		{
			if ( userId <= 0 || orgUnitId <= 0 ) return; // EXIT
			execNonQuery(
				"IF EXISTS ( SELECT * FROM organization_unit_user_assoc WHERE user_id = @user_id ) " +
				"BEGIN " +
					"UPDATE organization_unit_user_assoc " +
					"SET org_unit_id = @org_unit_id WHERE user_id = @user_id " +
				"END ELSE BEGIN " +
					"INSERT INTO organization_unit_user_assoc (org_unit_id, user_id) " +
					"VALUES (@org_unit_id, @user_id) " +
				"END",
				createSqlParam("@user_id", userId),
				createSqlParam("@org_unit_id", orgUnitId));
		}
		#endregion

		#endregion

		#region Domain Configuration
		public BindingListEx<Repository.Domain.ConfigurationPath> GetConfigurationPaths(int domainID)
		{
			if ( domainID <= 0 ) throw new ArgumentException("Invalid Domain ID (<= 0).", "domainID");

			const string query =
				"SELECT * FROM domain_configuration_paths WHERE domain_id = @domain_id";

			DataTable dt = fillDataTable(query, new SqlParameter("@domain_id", (object)domainID));
			BindingListEx<Repository.Domain.ConfigurationPath> result =
				new BindingListEx<Domain.ConfigurationPath>();
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					result.Add(new Domain.ConfigurationPath((int)row["config_path_id"], row["path"] as string));
				}
			}
			return result;
		}
		public void SetConfigurationPaths(int domainID, IEnumerable<Repository.Domain.ConfigurationPath> paths)
		{
			const string query =
				"IF EXISTS(SELECT * FROM domain_configuration_paths WHERE config_path_id = @config_path_id) " +
				"BEGIN " +
					"UPDATE domain_configuration_paths SET domain_id = @domain_id, path = @path " +
					"WHERE config_path_id = @config_path_id; " +
					"SELECT @config_path_id " +
				"END ELSE BEGIN " +
					"INSERT INTO domain_configuration_paths ( domain_id, path ) " +
					"VALUES ( @domain_id, @path ); " +
					"SELECT SCOPE_IDENTITY(); " +
				"END";
			const string querydelete =
				"DELETE FROM domain_configuration_paths " +
				"WHERE {0}domain_id = @domain_id";
			const string querynotin =
				"config_path_id NOT IN ({0}) AND ";

			string pathids = "";

			// insert / update paths
			foreach ( var item in paths ) {
				object newid = execScalar(query,
					new SqlParameter("@config_path_id", (object)item.PathID),
					new SqlParameter("@domain_id", (object)domainID),
					new SqlParameter("@path", item.Path));
				if ( newid != null ) {
					if ( pathids.Length > 0 ) pathids += ",";
					pathids += newid.ToString();
				}
			}
			// delete old paths
			string delquery;
			if ( string.IsNullOrEmpty(pathids) ) {
				delquery = string.Format(querydelete, "");
			} else {
				delquery = string.Format(querydelete, string.Format(querynotin, pathids));
			}
			execNonQuery(delquery, new SqlParameter("@domain_id", (object)domainID));
		}
		public Repository.Domain.ConfigurationPath GetConfigurationPath(int configPathID)
		{
			const string query =
				"SELECT * FROM domain_configuration_paths WHERE config_path_id = @config_path_id";

			Hashtable row = executeRow(query, new SqlParameter("@config_path_id", (object)configPathID));
			Repository.Domain.ConfigurationPath result = null;
			if ( row != null ) {
				result = new Domain.ConfigurationPath((int)row["config_path_id"], row["path"] as string);
			}
			return result;
		}
		#endregion

		#region Human Activities

		public DataTable GetHumanActivityUpdateServiceGuid()
		{
			string query = "SELECT * FROM configurations WHERE config_key = 'HumanActivityUpdate'";

			return fillDataTable(query);
		}
		public int GetHumanActivityPort()
		{
			string query = "SELECT config_value FROM configurations WHERE config_key = 'HumanActivityPort'";

			object obj = execScalar(query);
			if ( obj == null || !( obj is int ) ) return 30000;
			else return (int)obj;
		}

		#endregion

		#region KPIs

		public BindingListEx<KeyPerformanceIndicator> GetKeyPerformanceIndicatiors()
		{
			const string query =
				"SELECT * FROM key_performance_indicators WHERE kpi_nm != @nm AND " +
				"(org_id = 0 OR org_id = @org_id)";

			DataTable dt = fillDataTable(query,
				new SqlParameter("@nm", Resources.sNewKPI),
				new SqlParameter("@org_id", Global.CurrentUser.OrganizationID));
			return fillSSBList<KeyPerformanceIndicator>(dt);

		}

		public KeyPerformanceIndicator GetKeyPerformanceIndicatior(string name)
		{
			if ( string.IsNullOrEmpty(name) ) throw new ArgumentNullException(name);
			const string query =
				"SELECT kpi_id FROM key_performance_indicators WHERE kpi_nm = @nm";
			object obj = null;
			try {
				obj = execScalar(query, new SqlParameter("@nm", name));
				if ( obj == null ) return null;
			} catch { }
			return RepositoryService.GetItem<KeyPerformanceIndicator>((int)obj);
		}

		public BindingListEx<KPIMetric> GetMetrics(int kpiId)
		{
			string query = null;
			if ( kpiId > 0 ) {
				query = "SELECT * FROM kpi_metrics WHERE org_id = @org AND kpi_id = @kpi";
			} else {
				query = "SELECT * FROM kpi_metrics WHERE org_id = @org";
			}
			DataTable dt = fillDataTable(query,
				new SqlParameter("@org", Global.CurrentUser.OrganizationID),
				new SqlParameter("@kpi", kpiId));
			return fillSSBList<KPIMetric>(dt);
		}

		#endregion

		#region Others
		public DataTable GetDataTypes()
		{
			return fillDataTable("SELECT * FROM datatypes");
		}
		#endregion

		#region Worfklow DLLs
		public void UploadWorkflowDll(Assembly assembly)
		{
			if ( assembly == null || assembly.Location.IsNullOrEmpty() ) return;
			string query = string.Format("IF NOT EXISTS (SELECT * FROM wf_dlls WHERE wf_dll_fullname = @fullname){0}INSERT INTO wf_dlls(wf_dll_fullname, wf_dll_data) VALUES(@fullname, @dll_data){0}ELSE{0}UPDATE wf_dlls SET wf_dll_data = @dll_data WHERE wf_dll_fullname = @fullname", Environment.NewLine);
			execNonQuery(query,
				createSqlParam("fullname", assembly.FullName),
				createSqlParam("dll_data", assembly.ToByteArray()));
		}
		public Assembly GetWorkflowDll(string fullName)
		{
			string query = "SELECT wf_dll_data FROM wf_dlls WHERE wf_dll_fullname = @fullname";
			object obj = execScalar(query,
				createSqlParam("fullname", fullName));
			if ( obj == null || !( obj is byte[] ) ) return null;
			else return Assembly.Load(obj as byte[]);
		}
		public void DeleteWorfklowDll(string fullName)
		{
			string query = "DELETE FROM wf_dlls WHERE wf_dll_fullname = @fullname";
			execNonQuery(query,
				createSqlParam("fullname", fullName));
		}
		#endregion

		#region Tracking Helper
		public string GetTrackingConnection()
		{
			Guid trackingConnectionGuid = new Guid("43343C34-5054-4d85-BE55-EA33C0170E44");
			return GetValueFromConfigurations(trackingConnectionGuid);
		}
		public void SetTrackingConnection(string value)
		{
			Guid trackingConnectionGuid = new Guid("43343C34-5054-4d85-BE55-EA33C0170E44");
			SaveToConfigurations(trackingConnectionGuid, "trackingConnection", value);
		}
		#endregion

		#endregion

		#region Restriction
		internal string GetEncryptedRestrictionFromDB()
		{
			Guid restrictionGuid = new Guid("1D52DC97-E42F-44d6-905F-906B64C9FAFC");
			object key = GetValueFromConfigurations(restrictionGuid);
			return key == null ? "" : key.ToString();
		}
		internal bool SaveRestrictionToDB(string restrictionXml)
		{
			Guid restrictionGuid = new Guid("1D52DC97-E42F-44d6-905F-906B64C9FAFC");
			string selectQuery = "SELECT config_key FROM configurations WHERE config_guid = @guid";
			object key = execScalar(selectQuery, new SqlParameter("@guid", restrictionGuid));
			string keyString = Helpers.CompressString("restriction");
			if ( key == null ) {
#if DEBUG
				string query = "INSERT INTO configurations (config_guid, config_key, config_value) " +
					"VALUES (@guid, @key, @value)";
				execScalar(query,
					new SqlParameter("@guid", restrictionGuid),
					new SqlParameter("@key", keyString),
					new SqlParameter("@value", restrictionXml));
#else
                return false;
#endif
			} else {
				string query = "UPDATE configurations SET config_value = @value " +
					"WHERE config_guid = @guid AND config_key = @key";
				execScalar(query,
					new SqlParameter("@value", restrictionXml),
					new SqlParameter("@guid", restrictionGuid),
					new SqlParameter("@key", keyString));
			}
			return true;
		}
		#endregion

		#region Configurations Table Access
		/// <summary>
		/// Saves (insert or update) a value to the configurations table.
		/// </summary>
		/// <param name="configGuid">The config GUID used as identifier.</param>
		/// <param name="configKey">The config key.</param>
		/// <param name="value">The value.</param>
		protected void SaveToConfigurations(Guid configGuid, string configKey, string value)
		{
			string selectQuery = "SELECT config_key FROM configurations WHERE config_guid = @guid";
			object selectedKey = execScalar(selectQuery, new SqlParameter("@guid", configGuid));
			string keyString = Helpers.CompressString(configKey);
			if ( selectedKey == null ) {
				string query = "INSERT INTO configurations (config_guid, config_key, config_value) " +
									"VALUES (@guid, @key, @value)";
				execScalar(query,
					new SqlParameter("@guid", configGuid),
					new SqlParameter("@key", keyString),
					new SqlParameter("@value", value));
			} else {
				string query = "UPDATE configurations SET config_value = @value " +
					"WHERE config_guid = @guid AND config_key = @key";
				execScalar(query,
					new SqlParameter("@guid", configGuid),
					new SqlParameter("@key", keyString),
					new SqlParameter("@value", value));
			}
		}
		/// <summary>
		/// Gets the value from the configurations table.
		/// </summary>
		/// <param name="configGuid">The config GUID used as identifier.</param>
		/// <returns></returns>
		protected string GetValueFromConfigurations(Guid configGuid)
		{
			string selectQuery = "SELECT config_value FROM configurations WHERE config_guid = @guid";
			object result = execScalar(selectQuery, new SqlParameter("@guid", configGuid));
			return result == null ? "" : result.ToString();
		}
		#endregion

		#region Private Members

		#region Fields
		private UserAccessController _useraccess = null;
		private IServiceProvider _serviceProvider;

		private Dictionary<ItemType, string> _dicSelectAllQueries = new Dictionary<ItemType, string>();
		private Dictionary<ItemType, string> _dicSelectAllDomainQueries = new Dictionary<ItemType, string>();
		private Dictionary<ItemType, string> _dicSelectAllCompleteQueries = new Dictionary<ItemType, string>();
		private Dictionary<ItemType, string> _dicSelectItemQueries = new Dictionary<ItemType, string>();
		private Dictionary<ItemType, string> _dicSelectForNewQueries = new Dictionary<ItemType, string>();
		private Dictionary<ItemType, string> _dicUpdateQueries = new Dictionary<ItemType, string>();
		private Dictionary<ItemType, string> _dicDeleteQueries = new Dictionary<ItemType, string>();

		#endregion

		#region Methods
		private T xmlDeserializeObject<T>(string xmlstr) where T : class
		{
			try {
				if ( string.IsNullOrEmpty(xmlstr) )
					return null;
				XmlSerializer xs = new XmlSerializer(typeof(T));
				using ( StringReader sr = new StringReader(xmlstr) ) {
					return xs.Deserialize(sr) as T;
				}
			} catch {
				return null;
			}
		}
		private string xmlSerializeObject(object obj)
		{
			string xmlstr = null;
			if ( obj != null ) {
				XmlSerializer xs = new XmlSerializer(obj.GetType());
				XmlWriterSettings settings = new XmlWriterSettings();
				settings.Indent = false;
				settings.OmitXmlDeclaration = true;
				using ( StringWriter sw = new StringWriter() )
				using ( XmlWriter xw = XmlWriter.Create(sw, settings) ) {
					xs.Serialize(xw, obj);
					xmlstr = sw.ToString();
				}
			}
			return xmlstr;
		}

		private ItemType getItemTypeFromIRepositoryItemClass<T>() where T : class, IRepositoryItem
		{
			ItemType type = ItemType.Undefined;
			object[] attribs = typeof(T).GetCustomAttributes(typeof(RepositoryItemAttribute), false);
			foreach ( object attrib in attribs ) {
				RepositoryItemAttribute ria = attrib as RepositoryItemAttribute;
				if ( ria != null ) {
					type = ria.ItemType;
					break;
				}
			}
			if ( type == ItemType.Undefined ) {
				throw new Exception(
					string.Format("The type '{0}' does not include a valid RepositoryItemAttribute.",
								  typeof(T).FullName));
			}
			return type;
		}

		private object getValue(DataRow row, string columnname, object dbnullvalue)
		{
			if ( row == null || !row.Table.Columns.Contains(columnname) ) {
				return dbnullvalue;
			}
			return ( row.IsNull(columnname) ? dbnullvalue : row[columnname] );
		}
		private byte[] getBytes(DataRow row, string columnname)
		{
			return getValue(row, columnname, null) as byte[];
		}
		private string getString(DataRow row, string columnname, string dbnullvalue)
		{
			return (string)getValue(row, columnname, dbnullvalue);
		}
		private int getInt(DataRow row, string columnname, int dbnullvalue)
		{
			return (int)getValue(row, columnname, dbnullvalue);
		}
		private bool getBool(DataRow row, string columnname, bool dbnullvalue)
		{
			return (bool)getValue(row, columnname, dbnullvalue);
		}
		private DateTime getDateTime(DataRow row, string columnname, DateTime dbnullvalue)
		{
			return (DateTime)getValue(row, columnname, dbnullvalue);
		}
		private XmlSchema getSchema(DataRow row, string columnname)
		{
			XmlSchema schema = null;
			string xsd = getString(row, columnname, "");
			if ( xsd == null || xsd == "" ) return schema;
			using ( StringReader sr = new StringReader(xsd) ) {
				schema = XmlSchema.Read(sr,
					delegate(object sender, ValidationEventArgs e)
					{
						MessageBox.Show(e.Message, e.Exception.Source, MessageBoxButtons.OK, MessageBoxIcon.Error);
					});
			}
			return schema;
		}
		private XmlDocument getXmlDocument(DataRow row, string columnname)
		{
			XmlDocument xd = null;
			string xml = getString(row, columnname, "");
			if ( xml == null || xml == "" ) return xd;
			xd = new XmlDocument();
			xd.LoadXml(xml);
			return xd;
		}
		private XsltDocument getXsltDocument(DataRow row, string columnname)
		{
			string sXslt = getString(row, columnname, null);
			XsltDocument xslt = null;
			if ( !string.IsNullOrEmpty(sXslt) ) {
				xslt = new XsltDocument();
				xslt.LoadXml(sXslt);
			}
			return xslt;
		}
		private T getXmlSerializedObject<T>(DataRow row, string columnname) where T : class
		{
			try {
				string xmlstr = getString(row, columnname, null);
				if ( xmlstr == null || xmlstr == "" )
					return null;
				XmlSerializer xs = new XmlSerializer(typeof(T));
				using ( StringReader sr = new StringReader(xmlstr) ) {
					return xs.Deserialize(sr) as T;
				}
			} catch {
				return null;
			}
		}
		private T getSerializedObject<T>(DataRow row, string columnname) where T : class
		{
			try {
				string str = getString(row, columnname, null);
				if ( str == null || str == "" )
					return null;
				IFormatter f = new BinaryFormatter();
				using ( MemoryStream ms = new MemoryStream() ) {
					using ( StreamWriter sw = new StreamWriter(ms) ) {
						sw.Write(str);
					}
					ms.Position = 0;
					return f.Deserialize(ms) as T;
				}
			} catch {
				return null;
			}
		}
		private Guid getGuid(DataRow row, string columnname, Guid dbnullvalue)
		{
			return (Guid)getValue(row, columnname, dbnullvalue);
		}
		/// <summary>
		/// Creates a <see cref="T:OpenComposite.Base.Collections.BindingListEx`1"/> for a DataTable
		/// </summary>
		/// <typeparam name="T">Type of the <see cref="T:OpenComposite.Base.Collections.BindingListEx`1"/> to return</typeparam>
		/// <param name="dt">DataTable to Convert</param>
		/// <returns></returns>
		private BindingListEx<T> fillSSBList<T>(DataTable dt) where T : RepositoryItemBase
		{
			Type t = typeof(T);
			RepositoryItemAttribute attr = t.GetCustomAttributes(typeof(RepositoryItemAttribute), false)[0] as RepositoryItemAttribute;
			if ( attr == null ) throw new NullReferenceException("RepositoryItemAttribute not specified");

			BindingListEx<T> list = new BindingListEx<T>();
			if ( dt != null && dt.Rows.Count > 0 ) {
				foreach ( DataRow row in dt.Rows ) {
					T item = RepositoryService.GetItem<T>((int)row[attr.IdColumnName]);
					Verb verb = item as Verb;
					if ( verb != null && !verb.IsDeleted ) {
						if ( verb.CapabilityID < 0 && ( verb.Capability == null || verb.Capability.IsDeleted ) ) {
							verb.Delete(false);
							Global.Data.GetRepositoryHierarchyParentsList(verb);
						}
					}
					if ( item != null ) {
						list.Add(item);
					}
				}
			}
			return list;
		}

		private void setValue(DataRow row, string propName, string columnname, object value)
		{
			if ( row == null || !row.Table.Columns.Contains(columnname) )
				return;

			if ( value is int && (int)value <= 0 && columnname.EndsWith("_id") )
				value = null;

			object oldValue = row[columnname];
			if ( !oldValue.Equals(value) ) {
				row[columnname] = ( value == null ? (object)DBNull.Value : value );
			}
		}
		private void setSchema(DataRow row, string propName, string columnname, XmlSchema schema)
		{
			if ( schema != null ) {
				XmlWriterSettings settings = new XmlWriterSettings();
				settings.Indent = false;
				settings.OmitXmlDeclaration = true;
				settings.ConformanceLevel = ConformanceLevel.Auto;
				using ( StringWriter sw = new StringWriter() )
				using ( XmlWriter xw = XmlWriter.Create(sw, settings) ) {
					schema.Write(xw);
					xw.Flush();
					setValue(row, propName, columnname, sw.ToString());
				}
			} else {
				setValue(row, propName, columnname, null);
			}
		}
		private void setXmlDocument(DataRow row, string propName, string columnname, XmlDocument xd)
		{
			if ( xd != null && xd.DocumentElement != null ) {
				XmlWriterSettings settings = new XmlWriterSettings();
				settings.Indent = false;
				settings.OmitXmlDeclaration = true;
				settings.ConformanceLevel = ConformanceLevel.Auto;
				using ( StringWriter sw = new StringWriter() )
				using ( XmlWriter xw = XmlWriter.Create(sw, settings) ) {
					xd.DocumentElement.WriteTo(xw);
					xw.Flush();
					setValue(row, propName, columnname, sw.ToString());
				}
			} else {
				setValue(row, propName, columnname, null);
			}
		}
		private void setXmlSerializedObject(DataRow row, string propName, string columnname, object obj)
		{
			if ( obj == null ) {
				setValue(row, propName, columnname, obj);
			} else {
				XmlSerializer xs = new XmlSerializer(obj.GetType());
				XmlWriterSettings settings = new XmlWriterSettings();
				settings.Indent = false;
				settings.OmitXmlDeclaration = true;
				using ( StringWriter sw = new StringWriter() )
				using ( XmlWriter xw = XmlWriter.Create(sw, settings) ) {
					xs.Serialize(xw, obj);
					setValue(row, propName, columnname, sw.ToString());
				}
			}
		}
		private void setSerializedObject(DataRow row, string propName, string columnname, object obj)
		{
			if ( obj == null ) {
				setValue(row, propName, columnname, obj);
			} else {
				IFormatter f = new BinaryFormatter();
				using ( MemoryStream ms = new MemoryStream() ) {
					f.Serialize(ms, obj);
					ms.Position = 0;
					using ( StreamReader sr = new StreamReader(ms) ) {
						setValue(row, propName, columnname, sr.ReadToEnd());
					}
				}
			}
		}

		private void notifiyRepositoryChange(
			IRepositoryItem item, MemberInfo member, object oldValue, object newValue)
		{
			IRepositoryChangeService changeSvc = null;
			if ( item != null ) {
				changeSvc = item.GetService(typeof(IRepositoryChangeService))
					as IRepositoryChangeService;
				if ( changeSvc != null ) {
					changeSvc.OnRepositoryChanged(item, member, oldValue, newValue);
				}
			}
		}
		#endregion

		#endregion
	}

	#region Static Class: Global
	public static class Global
	{
		#region Initialization Methods
		public static bool Initialize(IServiceProvider serviceProvider)
		{
			bool bOK = false;
			try {
				UserAccessController uactrler = new UserAccessController();
				uactrler.ShowLoginForm(Helper.DesignerApplicationId);

				Application.DoEvents();

				OpenComposite.EII.Global.Data = new OpenComposite.EII.DataAccess(serviceProvider, uactrler);

				bOK = true;
			} catch ( Exception ex ) {
				Helper.ShowException(ex);
				bOK = false;
			}
			return bOK;
		}
		public static bool Initialize(IServiceProvider serviceProvider, IWin32Window owner,
									  string title, string subtitle, string version)
		{
			bool bOK = false;
			try {
				UserAccessController uactrler = new UserAccessController();
				DialogResult result =
					uactrler.ShowLoginForm(owner, title, subtitle, version, Helper.DesignerApplicationId);
				if ( result == DialogResult.OK ) {

					Application.DoEvents();

					OpenComposite.EII.Global.Data = new OpenComposite.EII.DataAccess(serviceProvider, uactrler);

					#region Init Current User
					User user = Global.Data.RepositoryService.GetItem<User>(Global.CurrentUser.UserID.Value);
					Global.CurrentUser.IsSystemAdministrator = user.IsSystemAdmin;
					Global.CurrentUser.OrganizationName = user.Organization.Name;
					Global.CurrentUser.FullName = user.FormattedName;
					#endregion

					bOK = true;
				} else {
					bOK = false;
				}
			} catch ( Exception ex ) {
				Helper.ShowException(ex);
				bOK = false;
			}
			return bOK;
		}
		#endregion

		#region Properties
		public static DataAccess Data
		{
			get { return _data; }
			set { _data = value; }
		}
		public static RepositoryService Repository
		{
			get { return _data.RepositoryService; }
		}

		public static LogInData CurrentUser
		{
			get
			{
				if ( _data != null && _data.UserAccess != null ) {
					return _data.UserAccess.UserData;
				} else {
					return null;
				}
			}
		}
		public static UserAccessController UserAccess
		{
			get { return _data.UserAccess; }
		}
		public static string ConnectionString
		{
			get { return _data.UserAccess.ConnectString; }
		}

		public static StatusInformations Status
		{
			get { return _status; }
		}
		public static CultureInfo UserDefaultCulture { get; set; }
		#endregion

		#region Private Variables
		private static DataAccess _data = null;
		private static StatusInformations _status = new StatusInformations();
		#endregion
	}
	#endregion

	#region Class: StatusInformations
	public class StatusInformations
	{
		#region Initialization
		public StatusInformations()
		{
			_idCurrentRepository = -1;
			_idCurrentDomain = -1;

			_currentMainItem = null;

			IsTfsInstalled = true;
		}
		#endregion

		#region Properties
		private int _idCurrentRepository;
		public int CurrentRepositoryId
		{
			get { return _idCurrentRepository; }
			set { _idCurrentRepository = value; }
		}
		private int _idCurrentDomain;
		public int CurrentDomainId
		{
			get { return _idCurrentDomain; }
			set { _idCurrentDomain = value; }
		}
		private RepositoryItemBase _currentMainItem;
		public RepositoryItemBase CurrentMainItem
		{
			get { return _currentMainItem; }
			set { _currentMainItem = value; }
		}
		private bool _isTfsInstalled = true;
		public bool IsTfsInstalled
		{
			get { return _isTfsInstalled; }
			set { _isTfsInstalled = value; }
		}
		/// <summary>
		/// Gets a value indicating whether any compatible source control software was found on this system.
		/// </summary>
		/// <value>
		/// 	<c>true</c> if this source control was found; otherwise, <c>false</c>.
		/// </value>
		public bool HasSourceControl
		{
			get { return IsTfsInstalled; }
		}
		#endregion
	}
	#endregion

}
